-- 增长人群品类曝光分析
-- 3类人群（内部/外部0层/外部裂变）对不同品类表现，groupby dt 人群 cate2 曝光top20的品类
WITH t_base AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,CASE   WHEN rootSourceId REGEXP 'longArticles_' AND b.root_source_id IS NULL THEN '公众号代运营-Daily'
                    WHEN rootSourceId REGEXP 'longArticles_' AND b.root_source_id IS NOT NULL THEN '公众号买号'
                    WHEN rootSourceId REGEXP 'dyyjs_' THEN '公众号代运营-即转'
                    WHEN rootSourceId REGEXP 'touliu_tencent_' THEN '小程序投流'
                    WHEN rootSourceId REGEXP 'touliu_tencentgzh_|touliu_tencentGzhArticle_|GzhTouLiu_Articles_gh' THEN '公众号投流'
                    WHEN rootSourceId REGEXP 'touliu_tencentqw_|WeCom_' THEN '测-企微投放'
                    WHEN rootSourceId REGEXP 'touliu_tencentwbqw_|dyyqw_' THEN '测-企微合作'
                    WHEN rootSourceId REGEXP 'gzhhz_' THEN '停-公众号合作'
                    WHEN rootSourceId REGEXP 'daitou_tencentgzh|DaiTou_gh' THEN '测-公众号完全代投放'
                    WHEN rootSourceId = '' OR rootSourceId IS NULL THEN '内部'
                    ELSE 'other'
            END AS channel
            ,page
            ,page_rec
            ,layer
            ,mid
            ,vid
            ,is_share
            ,share_cnt
            ,is_return_1
            ,is_return_n
            ,return_1_uv
            ,return_n_uv
            ,return_n_uv_noself
            ,new_exposure_cnt
            ,flowpool
    FROM    (
                SELECT  dt
                        ,apptype
                        ,rootsourceid
                        ,mid
                        ,vid
                        ,is_share
                        ,share_cnt
                        ,is_return_1
                        ,is_return_n
                        ,return_1_uv
                        ,return_n_uv
                        ,return_n_uv_noself
                        ,new_exposure_cnt
                        ,subsessionid
                        ,sessionid
                        ,extend
                        ,GET_JSON_OBJECT(extend,"$.extParams.userShareDepth") AS layer
                        ,page
                        ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                                WHEN page IN ("回流页","其他") THEN "非推荐"
                                ELSE "其他"
                        END AS page_rec
                        ,abcode
                        ,flowpool
                FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
                WHERE   dt BETWEEN "${start}" AND "${end}"
                AND     apptype IN ('4','0')
                AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页","回流页","其他")
                AND     abcode IN ("ab0","ab1","ab2","ab5","ab6","ab7","ab3","ab4","ab8","ab9")
                AND     abcode NOT IN ("ab100")
            ) a
    LEFT JOIN   (
                    SELECT  root_source_id
                    FROM    loghubods.changwen_rootsourceid_group_hour
                    WHERE   dt = MAX_PT('loghubods.changwen_rootsourceid_group_hour')
                    AND     group_name = '公众号买号'
                    GROUP BY root_source_id
                ) b
    ON      a.rootsourceid = b.root_source_id
    WHERE   a.page_rec = "推荐"
)
,t_base_data AS
(
    SELECT  ta.dt
            ,ta.in_out
            ,ta.vid
            ,ta.share_cnt
            ,ta.return_n_uv
            ,ta.new_exposure_cnt
            ,tb.merge_cate2
            ,tb.title_time_w_h_unionid
    FROM    (
                SELECT  dt
                        ,CASE   WHEN channel = "内部" THEN "内部"
                                WHEN layer = 0 THEN "外部0层"
                                WHEN layer > 0 THEN "外部裂变"
                                ELSE "其他"
                        END AS in_out
                        ,vid
                        ,is_share
                        ,share_cnt
                        ,is_return_1
                        ,is_return_n
                        ,return_1_uv
                        ,return_n_uv
                        ,return_n_uv_noself
                        ,new_exposure_cnt
                FROM    t_base
            ) ta
    LEFT JOIN   (
                    SELECT  vid
                            ,title_time_w_h_unionid
                            ,channel AS vid_channel
                            ,merge_cate1
                            ,merge_cate2
                            ,festive_label1
                            ,festive_label2
                    FROM    (
                                SELECT  vid
                                        ,COALESCE(GET_JSON_OBJECT(feature,"$.title_time_w_h_unionid"),"unknown") AS title_time_w_h_unionid
                                        ,COALESCE(GET_JSON_OBJECT(feature,"$.channel"),"unknown") AS channel
                                        ,COALESCE(GET_JSON_OBJECT(feature,"$.merge_first_level_cate"),"unknown") AS merge_cate1
                                        ,COALESCE(GET_JSON_OBJECT(feature,"$.merge_second_level_cate"),"unknown") AS merge_cate2
                                        ,COALESCE(GET_JSON_OBJECT(feature,"$.festive_label1"),"unknown") AS festive_label1
                                        ,COALESCE(GET_JSON_OBJECT(feature,"$.festive_label2"),"unknown") AS festive_label2
                                        ,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("${start}","00") AND CONCAT("${end}","23")
                            )
                    WHERE   rn = 1
                ) tb
    ON      ta.vid = tb.vid
)
,t_base_data_v1 AS
(
    SELECT  dt
            ,in_out
            ,merge_cate2
            ,SUM(1) AS exp
            ,SUM(share_cnt) AS share_cnt
            ,SUM(return_n_uv) AS return_n_uv
            ,SUM(new_exposure_cnt) AS new_exposure_cnt
    FROM    t_base_data
    GROUP BY dt
             ,in_out
             ,merge_cate2
)
,t_base_data_v2 AS
(
    SELECT  dt
            ,in_out
            ,merge_cate2
            ,exp
            ,share_cnt
            ,return_n_uv
            ,new_exposure_cnt
            ,round(COALESCE(share_cnt / exp,0),4) AS str
            ,round(COALESCE(return_n_uv / share_cnt,0),4) AS ros
            ,round(COALESCE(return_n_uv / exp,0),4) AS rovn
            ,round(COALESCE(new_exposure_cnt / exp,0),4) AS vov
            ,ROW_NUMBER() OVER (PARTITION BY dt,in_out ORDER BY exp DESC ) AS rn
    FROM    t_base_data_v1
)
SELECT  *
FROM    t_base_data_v2
WHERE   rn <= 20
AND     in_out <> "其他"
ORDER BY dt DESC,in_out,exp DESC
;
