-- click 视频粒度聚合 top100（按 max_depth 降序）+ 视频标题、发布时间
-- 使用: python fetch_daily.py .../04_click_top视频_by_depth.sql --date 20260210

SELECT t.dt, t.clickobjectid,
       v.title,
       TO_CHAR(FROM_UNIXTIME(v.publish_ts / 1000), 'yyyy-MM-dd HH:mm:ss') as publish_time,
       DATEDIFF(TO_DATE(t.dt, 'yyyyMMdd'), TO_DATE(TO_CHAR(FROM_UNIXTIME(v.publish_ts / 1000), 'yyyyMMdd'), 'yyyyMMdd'), 'dd') as days_since_pub,
       t.uv,  ROUND(t.uv  * 100.0 / SUM(t.uv)  OVER(PARTITION BY t.dt), 2) as uv_pct,
       t.cnt, ROUND(t.cnt * 100.0 / SUM(t.cnt) OVER(PARTITION BY t.dt), 2) as cnt_pct,
       t.max_depth, t.avg_depth
FROM (
    SELECT dt, clickobjectid,
           COUNT(DISTINCT machinecode) as uv,
           COUNT(1) as cnt,
           MAX(CAST(usersharedepth AS BIGINT)) as max_depth,
           ROUND(AVG(CAST(usersharedepth AS BIGINT)), 2) as avg_depth,
           ROW_NUMBER() OVER(PARTITION BY dt ORDER BY MAX(CAST(usersharedepth AS BIGINT)) DESC) as rn
    FROM loghubods.user_share_log
    WHERE dt = '${dt}' AND topic = 'click'
    GROUP BY dt, clickobjectid
) t
LEFT JOIN (
    SELECT id as vid, title, gmt_create_timestamp as publish_ts
    FROM videoods.wx_video
) v ON t.clickobjectid = v.vid
WHERE t.rn <= 100
ORDER BY t.max_depth DESC
