-- 抽样查看冲突 session，包含视频信息和时间戳
WITH t_head AS (
    SELECT  dt
            ,mid
            ,sessionid
            ,subsessionid
            ,rootsourceid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS in_out
            ,videoid
            ,title
            ,`merge一级品类` AS head_cate1
            ,`merge二级品类` AS head_cate2
            ,`点击时间` AS head_click_time
            ,pagesource
            ,`页面` AS head_page
    FROM    loghubods.opengid_base_data
    WHERE   dt = "${start}"
)
,t_rec AS (
    SELECT  dt
            ,mid
            ,sessionid
            ,subsessionid
            ,rootsourceid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS in_out
            ,vid
            ,page
            ,pagesource AS rec_pagesource
            ,ts AS rec_ts
            ,flowpool
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   dt = "${start}"
)
-- 先找出有冲突的 session
,t_conflict_sessions AS (
    SELECT  DISTINCT a.mid, a.sessionid
    FROM    t_head a
    JOIN    t_rec b
    ON      a.dt = b.dt AND a.mid = b.mid AND a.sessionid = b.sessionid
    WHERE   a.in_out <> b.in_out
    LIMIT   10
)
-- 展示这些 session 的所有记录
SELECT  'head' AS source
        ,a.dt
        ,a.mid
        ,a.sessionid
        ,a.subsessionid
        ,a.in_out
        ,a.rootsourceid
        ,a.videoid AS vid
        ,CAST(a.head_click_time AS STRING) AS ts
        ,a.head_page AS page
FROM    t_head a
JOIN    t_conflict_sessions c ON a.mid = c.mid AND a.sessionid = c.sessionid

UNION ALL

SELECT  'rec' AS source
        ,b.dt
        ,b.mid
        ,b.sessionid
        ,b.subsessionid
        ,b.in_out
        ,b.rootsourceid
        ,b.vid
        ,b.rec_ts AS ts
        ,b.page
FROM    t_rec b
JOIN    t_conflict_sessions c ON b.mid = c.mid AND b.sessionid = c.sessionid

ORDER BY mid, sessionid, subsessionid, ts
;
