-- 验证 opengid_base_data 和 dwd_recsys_alg_exposure_base 的关联情况（检查 in_out 冲突）
WITH t_head AS (
    -- 头部点击表
    SELECT  dt
            ,mid
            ,sessionid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS in_out
            ,COUNT(1) AS head_cnt
    FROM    loghubods.opengid_base_data
    WHERE   dt BETWEEN "${start}" AND "${end}"
    GROUP BY dt, mid, sessionid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END
)
,t_rec AS (
    -- 推荐曝光表
    SELECT  dt
            ,mid
            ,sessionid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS in_out
            ,COUNT(1) AS rec_cnt
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   dt BETWEEN "${start}" AND "${end}"
    GROUP BY dt, mid, sessionid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END
)
SELECT  COALESCE(a.dt, b.dt) AS dt
        ,a.in_out AS head_in_out
        ,b.in_out AS rec_in_out
        -- Session
        ,COUNT(DISTINCT CONCAT(COALESCE(a.mid, b.mid), COALESCE(a.sessionid, b.sessionid))) AS sessions
        -- UV
        ,COUNT(DISTINCT COALESCE(a.mid, b.mid)) AS uv
        -- 行数
        ,SUM(COALESCE(a.head_cnt, 0)) AS head_rows
        ,SUM(COALESCE(b.rec_cnt, 0)) AS rec_rows
FROM    t_head a
FULL OUTER JOIN t_rec b
ON      a.dt = b.dt
AND     a.mid = b.mid
AND     a.sessionid = b.sessionid
GROUP BY COALESCE(a.dt, b.dt)
        ,a.in_out
        ,b.in_out
ORDER BY dt, head_in_out, rec_in_out
;
