-- 探查 apptype=0 和 apptype=4 外部流量里，rootsourceid 前缀 × pagesource 的分布
-- 只按 (apptype, rootsourceid_prefix, pagesource, layer_type) 分组，避免 LIMIT 吃不下
-- 跑法：python fetch_daily.py tasks/00_尾号实验/探查_外部流量来源.sql --date 20260410 --force

SELECT  apptype
        ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部'
              ELSE SUBSTR(rootsourceid, 1, 4)
         END AS rootsourceid_prefix
        ,pagesource
        ,CASE WHEN GET_JSON_OBJECT(extend,'$.rootsessionid') = subsessionid
              THEN '首层' ELSE '裂变层'
         END AS layer_type
        ,COUNT(1) AS exp_cnt
        ,COUNT(DISTINCT mid) AS dau
FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
WHERE   dt = '${dt}'
AND     apptype IN ('0','4')
AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页","回流页","其他")
GROUP BY apptype
         ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部'
               ELSE SUBSTR(rootsourceid, 1, 4)
          END
         ,pagesource
         ,CASE WHEN GET_JSON_OBJECT(extend,'$.rootsessionid') = subsessionid
               THEN '首层' ELSE '裂变层'
          END
;
