-- 排名 1001~1020 的 from_mid（top1000 之外的 top20），每个取 10 条明细
-- 基于排除外部首层的逻辑
-- 使用: python fetch_daily.py .../11_click_low_from_mid_sample.sql --date 20260210

WITH t9 AS (
    SELECT  t1.root_source_id AS rootsourceid
           ,t1.channel
    FROM    loghubods.ad_put_flow_record_tencent_day t1
    LEFT JOIN loghubods.content_platform_account t2
    ON      t1.channel = t2.channel
    WHERE   t1.dt = MAX_PT('loghubods.ad_put_flow_record_tencent_day')
    AND     t1.put_type_one = '企微'
    AND     t1.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_|dyycd_'
    GROUP BY t1.root_source_id, t1.channel
),
-- 过滤后的 click 明细（排除自点 + 排除外部首层）
filtered AS (
    SELECT s.*,
           REGEXP_REPLACE(s.shareid, '-[^-]+-[^-]+$', '') as from_mid
    FROM loghubods.user_share_log s
    LEFT JOIN t9
    ON   s.rootsourceid = t9.rootsourceid
    WHERE s.dt = '${dt}' AND s.topic = 'click'
      AND REGEXP_REPLACE(s.shareid, '-[^-]+-[^-]+$', '') <> s.machinecode
      AND NOT (
          (s.rootsourceid REGEXP 'touliu_tencentwbqw_|dyyqw_'
           AND t9.channel REGEXP 'xycsd|csaq|shy|jxjx|gzcr|xyjj|jxatm|xjcy|yqyx|hbwq|jxxm|gzmy|cdjh|gzjr|gzxts|twhc|qdjdz|sjzyd|gzyhc|djh|gzlx|yywl|szjn|gzdd1|cqqd|cqslh|hzjy|hzjh|sclh|xyhc|snss'
           AND CAST(s.usersharedepth AS BIGINT) <= 1)
          OR
          (s.rootsourceid REGEXP 'touliu_tencentwbqw_|dyyqw_'
           AND (t9.channel IS NULL OR t9.channel NOT REGEXP 'xycsd|csaq|shy|jxjx|gzcr|xyjj|jxatm|xjcy|yqyx|hbwq|jxxm|gzmy|cdjh|gzjr|gzxts|twhc|qdjdz|sjzyd|gzyhc|djh|gzlx|yywl|szjn|gzdd1|cqqd|cqslh|hzjy|hzjh|sclh|xyhc|snss')
           AND CAST(s.usersharedepth AS BIGINT) = 0)
          OR
          (s.rootsourceid NOT REGEXP 'touliu_tencentwbqw_|dyyqw_'
           AND CAST(s.usersharedepth AS BIGINT) = 0)
      )
),
-- 按 from_mid 排名
ranked_mid AS (
    SELECT from_mid,
           COUNT(DISTINCT machinecode) as target_uv,
           ROW_NUMBER() OVER(ORDER BY COUNT(DISTINCT machinecode) DESC) as rn
    FROM filtered
    GROUP BY from_mid
),
-- 取排名 1001~1020
low_mids AS (
    SELECT from_mid, target_uv, rn
    FROM ranked_mid
    WHERE rn > 1000 AND rn <= 1020
)
-- 每个 from_mid 取 10 条明细
SELECT lm.rn as mid_rank, lm.target_uv, f.*
FROM (
    SELECT f.*,
           ROW_NUMBER() OVER(PARTITION BY f.from_mid ORDER BY CAST(f.usersharedepth AS BIGINT) DESC) as row_rn
    FROM filtered f
    JOIN low_mids lm ON f.from_mid = lm.from_mid
) f
JOIN low_mids lm ON f.from_mid = lm.from_mid
WHERE f.row_rn <= 10
ORDER BY lm.rn, f.row_rn
