-- click 分享者(from_mid) top1000，按带回的 target_mid 人数降序
-- from_mid = shareid 去掉最后两段（-code-timestamp），因为 mid 本身可能含 -
-- 示例: weixin_openid_o0w175QzI9bWlWjFv5dVzFWz9vO0-e200-1772196892078 → weixin_openid_o0w175QzI9bWlWjFv5dVzFWz9vO0
-- 使用: python fetch_daily.py .../05_click_top_from_mid_v2.sql --date 20260210

SELECT dt, apptype, from_mid,
       target_uv,
       ROUND(target_uv * 100.0 / SUM(target_uv) OVER(PARTITION BY dt, apptype), 2) as target_uv_pct,
       cnt,
       vid,
       max_depth, avg_depth
FROM (
    SELECT dt,
           apptype,
           REGEXP_REPLACE(shareid, '-[^-]+-[^-]+$', '') as from_mid,
           COUNT(DISTINCT machinecode) as target_uv,
           COUNT(1) as cnt,
           COUNT(DISTINCT clickobjectid) as vid,
           MAX(CAST(usersharedepth AS BIGINT)) as max_depth,
           ROUND(AVG(CAST(usersharedepth AS BIGINT)), 2) as avg_depth,
           ROW_NUMBER() OVER(PARTITION BY dt, apptype ORDER BY COUNT(DISTINCT machinecode) DESC) as rn
    FROM loghubods.user_share_log
    WHERE dt = '${dt}' AND topic = 'click'
      AND REGEXP_REPLACE(shareid, '-[^-]+-[^-]+$', '') <> machinecode  -- 排除自点
      AND (rootsourceid = '' OR rootsourceid IS NULL)  -- 排除转发带回的
    GROUP BY dt, apptype, REGEXP_REPLACE(shareid, '-[^-]+-[^-]+$', '')
) t
WHERE rn <= 1000
ORDER BY apptype, target_uv DESC
