
-- share_log click 回流来源分布
-- LEFT JOIN 活跃表，按 push_type + channel_type 分组

SELECT  s.dt
       ,COALESCE(a.push_type, '未匹配') AS push_type
       ,COALESCE(a.channel_type, '未匹配') AS channel_type
       ,COUNT(1) AS cnt
       ,COUNT(DISTINCT s.machinecode) AS uv
       ,COUNT(DISTINCT s.clickobjectid) AS vid_cnt
       ,MAX(CAST(s.usersharedepth AS BIGINT)) AS max_depth
       ,AVG(CAST(s.usersharedepth AS BIGINT)) AS avg_depth
FROM loghubods.user_share_log s
LEFT JOIN loghubods.ods_user_active_log_info_day a
  ON  s.dt = a.dt
  AND s.machinecode = a.machinecode
  AND s.subsessionid = a.subsessionid
WHERE s.dt = '${dt}'
  AND s.topic = 'click'
GROUP BY s.dt
        ,COALESCE(a.push_type, '未匹配')
        ,COALESCE(a.channel_type, '未匹配')
ORDER BY cnt DESC
