-- 按渠道（rootsourceid 前 4 位）拆 01 实验桶 vs 89 对照桶的 lift
-- 只看"推荐"page，不分首层/裂变层（先简化）
-- 跑法：python fetch_daily.py tasks/00_尾号实验/探查_渠道lift.sql --start 20260404 --end 20260415 --force
--
-- 验证假设：
--   1. 产品 4 的主渠道 dyyj 在异常期 cn_rov 稳/涨
--   2. 产品 0 的主渠道 toul/dyyq 在异常期 cn_rov 跌
--   3. 两个产品在 dyyj 渠道的响应应该一致（同样渠道同样反应）

SELECT  dt
        ,apptype
        ,CASE WHEN SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),
                          LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) IN ('0','1')
              THEN '01'
              WHEN SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),
                          LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) IN ('8','9')
              THEN '89'
              ELSE 'other'
         END AS suffix_group
        ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部'
              ELSE SUBSTR(rootsourceid, 1, 4)
         END AS channel
        ,COUNT(1) AS exp
        ,COUNT(DISTINCT mid) AS dau
        ,SUM(return_n_uv) AS return_n_uv
        ,SUM(is_share) AS is_share
        ,SUM(share_cnt) AS share_cnt
        ,COALESCE(SUM(return_n_uv) / COUNT(1), 0) AS bn_rov
FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
WHERE   dt = '${dt}'
AND     apptype IN ('0','4')
AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页")  -- 推荐 page
AND     SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),
               LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) IN ('0','1','8','9')
GROUP BY dt
         ,apptype
         ,CASE WHEN SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),
                           LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) IN ('0','1')
               THEN '01'
               WHEN SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),
                           LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) IN ('8','9')
               THEN '89'
               ELSE 'other'
          END
         ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部'
               ELSE SUBSTR(rootsourceid, 1, 4)
          END
;
