-- ════════════════════════════════════════════════════════════════════════════
-- exp_per_dau 按用户活跃度分级拆解
-- 用户分级：过去7天在 useractive_log 中的活跃天数
--   高活(5-7天) / 中活(2-4天) / 低活(1天) / 新用户(0天，��当天出现)
-- ════════════════════════════════════════════════════════════════════════════
WITH t_suffix_group AS
(
    SELECT "a" AS suffix, "ab" AS suffix_group
    UNION ALL SELECT "b", "ab"
    UNION ALL SELECT "0", "01"
    UNION ALL SELECT "1", "01"
    UNION ALL SELECT "2", "2c"
    UNION ALL SELECT "c", "2c"
    UNION ALL SELECT "3", "34"
    UNION ALL SELECT "4", "34"
    UNION ALL SELECT "5", "5d"
    UNION ALL SELECT "d", "5d"
    UNION ALL SELECT "6", "67"
    UNION ALL SELECT "7", "67"
    UNION ALL SELECT "8", "89"
    UNION ALL SELECT "9", "89"
    UNION ALL SELECT "e", "ef"
    UNION ALL SELECT "f", "ef"
)
,t_experiment_map AS
(
    -- ── apptype = 4 ──────────────────────────────────────────────────
    SELECT "4" AS apptype, "ab" AS suffix_group, "实验组：变更str*ros建模目标实验 有问题" AS abcode, "20260413" AS start_dt, "20260415" AS end_dt
    UNION ALL SELECT "4", "ab", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验", "20260320", "20260410"
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验", "20260330", "20260410"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验", "20260407", "20260410"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验", "20260407", "20260410"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "67", "实验组：bn_ros新损失函数",        "20260311", "20260319"
    UNION ALL SELECT "4", "5d", "实验组：解构特征排序str模型",     "20260314", "20260406"
    UNION ALL SELECT "4", "ef", "实验组：解构特征排序str模型&召回", "20260314", "20260320"
    UNION ALL SELECT "4", "ef", "实验组：DNN模型",                 "20260407", "29991231"
    UNION ALL SELECT "4", "2c", "实验组：DNN模型-调参",            "20260413", "29991231"
    UNION ALL SELECT "4", "89", "对照组",                          "20260301", "299991231"

    -- ── apptype = 0 ──────────────────────────────────────────────────
    UNION ALL SELECT "0", "ab", "实验组：变更str*ros建模目标实验 有问题", "20260413", "20260415"
    UNION ALL SELECT "0", "ab", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "ab", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验", "20260320", "20260410"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验", "20260330", "20260410"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "34", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验", "20260330", "20260410"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "67", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "5d", "实验组：变更str*ros建模目标实验", "20260407", "20260410"
    UNION ALL SELECT "0", "5d", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "5d", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "5d", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "ef", "实验组：DNN模型-调参",            "20260410", "29991231"
    UNION ALL SELECT "0", "2c", "实验组：DNN模型",                 "20260413", "29991231"
    UNION ALL SELECT "0", "89", "对照组",                          "20260301", "29991231"
)
-- 过去7天用户活跃天数
,t_user_history AS
(
    SELECT  machinecode AS mid
            ,apptype
            ,COUNT(DISTINCT dt) AS active_days_7d
    FROM    loghubods.useractive_log
    WHERE   dt >= TO_CHAR(DATEADD(TO_DATE('${dt}','yyyymmdd'), -7, 'dd'), 'yyyymmdd')
    AND     dt <  '${dt}'
    AND     apptype IN ("0","4")
    GROUP BY machinecode, apptype
)
,t_base AS
(
    SELECT  sub.*
            ,sg.suffix_group
            ,COALESCE(m.abcode,"对照组") AS abcode
            ,CASE
                WHEN h.active_days_7d >= 5 THEN '1_高活'
                WHEN h.active_days_7d >= 2 THEN '2_中活'
                WHEN h.active_days_7d >= 1 THEN '3_低活'
                ELSE '4_新用户'
             END AS user_tier
    FROM    (
                SELECT  dt
                        ,apptype
                        ,SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) AS suffix
                        ,a.mid
                        ,a.vid
                FROM    loghubods.dwd_recsys_alg_exposure_base_20250108 a
                WHERE   dt="${dt}"
                AND     apptype IN ("0","4")
                AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页")
                AND     abcode NOT IN ("ab100")
                AND     (apptype = "4"
                         OR abcode IN ("ab0","ab1","ab2","ab3","ab4","ab8","ab9"))
                AND     (apptype = "0"
                         OR abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9"))
            ) sub
    INNER JOIN t_suffix_group sg
    ON      sub.suffix = sg.suffix
    LEFT JOIN t_experiment_map m
    ON      sub.apptype = m.apptype
    AND     sg.suffix_group = m.suffix_group
    AND     '${dt}' BETWEEN m.start_dt AND m.end_dt
    LEFT JOIN t_user_history h
    ON      sub.mid = h.mid
    AND     sub.apptype = h.apptype
)
-- 按单尾号 × user_tier 聚合
,t_bucket AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,suffix
            ,user_tier
            ,COUNT(1) AS exp
            ,COUNT(DISTINCT mid) AS dau
            ,COALESCE(COUNT(1) / COUNT(DISTINCT mid), 0) AS exp_per_dau
    FROM    t_base
    GROUP BY dt, apptype, abcode, suffix_group, suffix, user_tier
)
-- 按 suffix_group 求尾号均值
,t_metrics AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,user_tier
            ,WM_CONCAT(DISTINCT ',', suffix) AS suffix
            ,AVG(exp) AS exp
            ,AVG(dau) AS dau
            ,ROUND(AVG(exp_per_dau), 2) AS exp_per_dau
            -- 各tier的dau占比需要跨tier算，在外层处理
    FROM    t_bucket
    GROUP BY dt, apptype, abcode, suffix_group, user_tier
)
-- 加上总 dau 算各 tier 占比
,t_total_dau AS
(
    SELECT  dt, apptype, abcode, suffix_group
            ,SUM(dau) AS total_dau
    FROM    t_metrics
    GROUP BY dt, apptype, abcode, suffix_group
)
,t_with_pct AS
(
    SELECT  m.*
            ,ROUND(m.dau / t.total_dau, 6) AS dau_pct
    FROM    t_metrics m
    JOIN    t_total_dau t
    ON      m.dt = t.dt
    AND     m.apptype = t.apptype
    AND     m.abcode = t.abcode
    AND     m.suffix_group = t.suffix_group
)
-- 基线桶（89）
,t_ctrl AS
(
    SELECT  dt, apptype, user_tier
            ,exp_per_dau AS ctrl_exp_per_dau
            ,dau_pct AS ctrl_dau_pct
    FROM    t_with_pct
    WHERE   suffix_group = '89'
)
SELECT  r.dt
        ,r.apptype
        ,r.abcode
        ,r.suffix_group
        ,r.suffix
        ,r.user_tier
        ,r.exp
        ,r.dau
        ,r.dau_pct
        ,r.exp_per_dau
        -- vs 89
        ,ROUND(r.dau_pct - ctrl.ctrl_dau_pct, 6) AS dau_pct_diff
        ,ROUND(r.exp_per_dau / NULLIF(ctrl.ctrl_exp_per_dau, 0) - 1, 6) AS exp_per_dau_diff
FROM    t_with_pct r
LEFT JOIN t_ctrl ctrl
ON      r.dt = ctrl.dt
AND     r.apptype = ctrl.apptype
AND     r.user_tier = ctrl.user_tier
ORDER BY r.dt DESC, r.apptype, r.suffix_group, r.user_tier
;
