-- 渠道场景分布分析
-- 查看各渠道×场景组合的UV分布

SELECT  channel
        ,hotsencetype
        ,COUNT(DISTINCT mid) AS 点击uv
        ,COUNT(DISTINCT CASE WHEN 是否进入推荐 = '1' THEN mid END) / COUNT(DISTINCT mid) AS 进入分发率
        ,(SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv ELSE 0 END)
          + SUM(CASE WHEN 再分享单聊回流uv > 0 THEN 再分享单聊回流uv ELSE 0 END)
         ) / (COUNT(DISTINCT mid) + 10) AS 整体裂变率
FROM    loghubods.opengid_base_data
WHERE   dt >= ${start}
AND     dt <= ${end}
AND     usersharedepth = 0
AND     videoid IS NOT NULL
GROUP BY channel, hotsencetype
ORDER BY 点击uv DESC
;
