
-- click 按 内部/外部 + 外部首层/非外部首层 分布
-- 内部判定: rootsourceid = '' OR rootsourceid IS NULL
-- 外部首层判定: 复用 ods_user_active_log_info_day 的 level_type 逻辑

WITH t9 AS (
    SELECT  t1.root_source_id AS rootsourceid
           ,t1.channel
    FROM    loghubods.ad_put_flow_record_tencent_day t1
    LEFT JOIN loghubods.content_platform_account t2
    ON      t1.channel = t2.channel
    WHERE   t1.dt = MAX_PT('loghubods.ad_put_flow_record_tencent_day')
    AND     t1.put_type_one = '企微'
    AND     t1.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_|dyycd_'
    GROUP BY t1.root_source_id, t1.channel
)

SELECT dt,
       COALESCE(source_type, '合计') AS source_type,
       COALESCE(level_type, '合计') AS level_type,
       COUNT(DISTINCT machinecode) AS uv, COUNT(1) AS cnt
FROM (
    SELECT s.dt, s.machinecode,
           CASE WHEN s.rootsourceid = '' OR s.rootsourceid IS NULL
                THEN '内部' ELSE '外部' END AS source_type,
           CASE
               WHEN s.rootsourceid = '' OR s.rootsourceid IS NULL
                   THEN '内部'
               WHEN s.rootsourceid REGEXP 'touliu_tencentwbqw_|dyyqw_'
                   AND t9.channel REGEXP 'xycsd|csaq|shy|jxjx|gzcr|xyjj|jxatm|xjcy|yqyx|hbwq|jxxm|gzmy|cdjh|gzjr|gzxts|twhc|qdjdz|sjzyd|gzyhc|djh|gzlx|yywl|szjn|gzdd1|cqqd|cqslh|hzjy|hzjh|sclh|xyhc|snss'
                   AND CAST(s.usersharedepth AS BIGINT) <= 1
                   THEN '外部首层'
               WHEN s.rootsourceid REGEXP 'touliu_tencentwbqw_|dyyqw_'
                   AND (t9.channel IS NULL OR t9.channel NOT REGEXP 'xycsd|csaq|shy|jxjx|gzcr|xyjj|jxatm|xjcy|yqyx|hbwq|jxxm|gzmy|cdjh|gzjr|gzxts|twhc|qdjdz|sjzyd|gzyhc|djh|gzlx|yywl|szjn|gzdd1|cqqd|cqslh|hzjy|hzjh|sclh|xyhc|snss')
                   AND CAST(s.usersharedepth AS BIGINT) = 0
                   THEN '外部首层'
               WHEN s.rootsourceid NOT REGEXP 'touliu_tencentwbqw_|dyyqw_'
                   AND CAST(s.usersharedepth AS BIGINT) = 0
                   THEN '外部首层'
               ELSE '外部非首层'
           END AS level_type
    FROM loghubods.user_share_log s
    LEFT JOIN t9
    ON   s.rootsourceid = t9.rootsourceid
    WHERE s.dt = '${dt}' AND s.topic = 'click'
) t
GROUP BY dt, source_type, level_type
GROUPING SETS ((dt, source_type, level_type), (dt, source_type), (dt, level_type), (dt))
ORDER BY source_type, level_type
