-- click 按用户 max depth 分桶分布
-- 每用户取当天最大 usersharedepth；0~50 逐个展示，>50 归一桶
-- vid 为桶内去重 clickobjectid
-- 使用: python fetch_daily.py .../02_click_depth分布.sql --date 20260210

SELECT dt, depth_bucket,
       uv,  ROUND(uv  * 100.0 / SUM(uv)  OVER(PARTITION BY dt), 2) as uv_pct,
       cnt, ROUND(cnt * 100.0 / SUM(cnt) OVER(PARTITION BY dt), 2) as cnt_pct,
       vid, ROUND(vid * 100.0 / SUM(vid) OVER(PARTITION BY dt), 2) as vid_pct,
       actual_max
FROM (
    SELECT t.dt,
           CASE WHEN max_depth <= 50 THEN max_depth ELSE 51 END as depth_bucket,
           COUNT(DISTINCT machinecode) as uv,
           COUNT(1) as cnt,
           COUNT(DISTINCT clickobjectid) as vid,
           MAX(max_depth) as actual_max
    FROM (
        SELECT dt, machinecode, clickobjectid,
               MAX(CAST(usersharedepth AS BIGINT)) OVER(PARTITION BY dt, machinecode) as max_depth
        FROM loghubods.user_share_log
        WHERE dt = '${dt}' AND topic = 'click'
    ) t
    GROUP BY t.dt, CASE WHEN max_depth <= 50 THEN max_depth ELSE 51 END
) agg
ORDER BY depth_bucket
