
-- click 排除外部首层后的 分享者(from_mid) top1000，按带回的 target_mid 人数降序
-- 保留: 内部 + 外部非首层
-- 使用: python fetch_daily.py .../10_click_top_from_mid_排除外部首层.sql --date 20260209

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
)

SELECT dt, from_mid,
       target_uv,
       ROUND(target_uv * 100.0 / SUM(target_uv) OVER(PARTITION BY dt), 2) as target_uv_pct,
       cnt,
       vid,
       max_depth, avg_depth
FROM (
    SELECT s.dt,
           split(s.shareid, '-')[0] as from_mid,
           COUNT(DISTINCT s.machinecode) as target_uv,
           COUNT(1) as cnt,
           COUNT(DISTINCT s.clickobjectid) as vid,
           MAX(CAST(s.usersharedepth AS BIGINT)) as max_depth,
           ROUND(AVG(CAST(s.usersharedepth AS BIGINT)), 2) as avg_depth,
           ROW_NUMBER() OVER(PARTITION BY s.dt ORDER BY COUNT(DISTINCT s.machinecode) DESC) as rn
    FROM loghubods.user_share_log s
    LEFT JOIN t9
    ON   s.rootsourceid = t9.rootsourceid
    WHERE s.dt = '${dt}' AND s.topic = 'click'
      AND split(s.shareid, '-')[0] <> 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)
      )
    GROUP BY s.dt, split(s.shareid, '-')[0]
) t
WHERE rn <= 1000
ORDER BY target_uv DESC
