-- 验证 shareid 末尾是否为分享时间戳
-- shareid 格式: {mid}-{code}-{timestamp_ms}
-- 提取最后一段作为毫秒时间戳，转为日期后与 share 记录的 dt 对比
-- 使用: python run_sql.py .../05_verify_shareid_timestamp.sql

WITH clicks AS (
    SELECT shareid
    FROM loghubods.user_share_log
    WHERE dt = '${start}'
      AND topic = 'click'
      AND shareid IS NOT NULL AND shareid <> ''
    GROUP BY shareid
),
shares AS (
    -- 回溯 90 天找 share 记录
    SELECT shareid, MIN(dt) AS share_dt
    FROM loghubods.user_share_log
    WHERE dt >= TO_CHAR(DATEADD(TO_DATE('${start}', 'yyyyMMdd'), -90, 'dd'), 'yyyyMMdd')
      AND dt <= '${start}'
      AND topic = 'share'
      AND shareid IS NOT NULL AND shareid <> ''
    GROUP BY shareid
)
SELECT
    s.share_dt,
    TO_CHAR(FROM_UNIXTIME(CAST(REGEXP_EXTRACT(c.shareid, '-([^-]+)$', 1) AS BIGINT) / 1000), 'yyyyMMdd') AS ts_date,
    CASE WHEN s.share_dt = TO_CHAR(FROM_UNIXTIME(CAST(REGEXP_EXTRACT(c.shareid, '-([^-]+)$', 1) AS BIGINT) / 1000), 'yyyyMMdd')
         THEN 'match' ELSE 'mismatch' END AS result,
    COUNT(1) AS cnt
FROM clicks c
JOIN shares s ON c.shareid = s.shareid
GROUP BY
    s.share_dt,
    TO_CHAR(FROM_UNIXTIME(CAST(REGEXP_EXTRACT(c.shareid, '-([^-]+)$', 1) AS BIGINT) / 1000), 'yyyyMMdd')
ORDER BY cnt DESC
