
-- share_log click LEFT JOIN 活跃表
-- 按 level_type / channel_type / channel_type_frist 分组看占比

SELECT  s.dt
       ,COALESCE(a.level_type, '未匹配') AS level_type
       ,COALESCE(a.channel_type, '未匹配') AS channel_type
       ,COALESCE(a.channel_type_frist, '未匹配') AS channel_type_frist
       ,COUNT(1) AS cnt
       ,ROUND(COUNT(1) * 100.0 / SUM(COUNT(1)) OVER(PARTITION BY s.dt), 2) AS cnt_pct
       ,COUNT(DISTINCT s.machinecode) AS uv
       ,ROUND(COUNT(DISTINCT s.machinecode) * 100.0
              / SUM(COUNT(DISTINCT s.machinecode)) OVER(PARTITION BY s.dt), 2) AS uv_pct
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.level_type, '未匹配')
        ,COALESCE(a.channel_type, '未匹配')
        ,COALESCE(a.channel_type_frist, '未匹配')
ORDER BY cnt DESC
