-- 单日查询：头部品类从增长表(opengid_base_data)获取
-- 参数: ${dt} - 日期，格式 YYYYMMDD
WITH t_head AS (
    SELECT  dt
            ,mid
            ,subsessionid
            ,rootsourceid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS head_in_out
            ,videoid AS head_vid
            ,UNIX_TIMESTAMP(`点击时间`) AS click_ts
            ,`merge一级品类` AS head_cate1
            ,`merge二级品类` AS head_cate2
            ,channel AS head_channel
    FROM    loghubods.opengid_base_data
    WHERE   dt = "${dt}"
)
,t_rec AS (
    SELECT  dt
            ,mid
            ,subsessionid
            ,headvideoid
            ,ts
            ,rootsourceid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS rec_in_out
            ,vid AS rec_vid
            ,page
            ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                    WHEN page IN ("回流页","其他") THEN "非推荐"
                    ELSE "其他"
            END AS page_rec
            ,share_cnt
            ,return_n_uv
            ,new_exposure_cnt
            ,GET_JSON_OBJECT(extend,"$.extParams.userShareDepth") AS layer
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   dt = "${dt}"
    AND     apptype IN ('4','0')
    AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页","回流页","其他")
)
,t_joined_raw AS (
    SELECT  r.dt
            ,r.mid
            ,r.subsessionid
            ,r.rec_vid
            ,r.ts
            ,r.rec_in_out
            ,r.page
            ,r.page_rec
            ,r.share_cnt
            ,r.return_n_uv
            ,r.new_exposure_cnt
            ,r.layer
            ,h.head_in_out
            ,h.head_vid
            ,h.head_cate1
            ,h.head_cate2
            ,h.head_channel
            ,ROW_NUMBER() OVER (
                PARTITION BY r.dt, r.mid, r.subsessionid, r.rec_vid, r.ts
                ORDER BY h.click_ts DESC
            ) AS rn
    FROM    t_rec r
    LEFT JOIN t_head h
    ON      r.dt = h.dt
    AND     r.mid = h.mid
    AND     r.subsessionid = h.subsessionid
    AND     r.headvideoid = h.head_vid
    AND     h.click_ts <= CAST(r.ts AS BIGINT)
    WHERE   r.page_rec = '推荐'
)
,t_joined AS (
    SELECT  dt
            ,COALESCE(head_in_out, rec_in_out) AS in_out
            ,CASE   WHEN COALESCE(head_in_out, rec_in_out) = '内部' THEN '内部'
                    WHEN layer = '0' THEN '外部0层'
                    WHEN CAST(layer AS INT) > 0 THEN '外部裂变'
                    ELSE '其他'
            END AS crowd
            ,head_vid
            ,head_cate1
            ,CASE   WHEN head_vid IS NULL THEN '未关联头部'
                    WHEN head_cate2 IS NULL OR head_cate2 = '' THEN 'unknown'
                    ELSE head_cate2
            END AS head_cate2
            ,head_channel
            ,rec_vid
            ,share_cnt
            ,return_n_uv
            ,new_exposure_cnt
    FROM    t_joined_raw
    WHERE   rn = 1
)
,t_vid_info AS (
    SELECT  vid
            ,COALESCE(GET_JSON_OBJECT(feature,"$.merge_second_level_cate"),"unknown") AS rec_cate2
    FROM    (
                SELECT  vid
                        ,feature
                        ,ROW_NUMBER() OVER (PARTITION BY vid ORDER BY dt DESC, hh DESC) AS rn
                FROM    loghubods.alg_vid_feature_basic_info
                WHERE   CONCAT(dt,hh) BETWEEN CONCAT(TO_CHAR(DATEADD(TO_DATE("${dt}",'yyyymmdd'),-1,'dd'),'yyyymmdd'),"00")
                                          AND CONCAT(TO_CHAR(DATEADD(TO_DATE("${dt}",'yyyymmdd'),1,'dd'),'yyyymmdd'),"23")
            )
    WHERE   rn = 1
)
,t_final AS (
    SELECT  a.dt
            ,a.crowd
            ,a.head_cate2
            ,b.rec_cate2
            ,SUM(1) AS exp
            ,SUM(a.share_cnt) AS share_cnt
            ,SUM(a.return_n_uv) AS return_n_uv
            ,SUM(a.new_exposure_cnt) AS new_exposure_cnt
    FROM    t_joined a
    LEFT JOIN t_vid_info b ON a.rec_vid = b.vid
    GROUP BY a.dt, a.crowd, a.head_cate2, b.rec_cate2
)
SELECT  dt
        ,crowd
        ,head_cate2
        ,rec_cate2
        ,exp
        ,share_cnt
        ,return_n_uv
        ,new_exposure_cnt
FROM    t_final
WHERE   crowd <> '其他'
AND     exp >= 1000
ORDER BY crowd, exp DESC
;
