-- 素材+视频维度分析
-- 链路：长文 → 卡片 → 点击视频 → 进入推荐 → 再分享 → 回流
-- 每个渠道取 Top 500

SELECT  *
FROM    (
            SELECT  dt
                    ,channel
                    -- 长文维度
                    ,rootsourceid
                    ,wx_sn
                    ,公众号名
                    ,文章标题
                    ,contenturl
                    -- 卡片维度
                    ,shareid
                    ,分享标题
                    ,分享封面
                    -- 视频维度
                    ,videoid
                    ,title
                    ,merge一级品类
                    ,merge二级品类
                    -- 核心指标
                    ,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 再分享回流率
                    ,(SUM(CASE WHEN 是否原视频 = '是' THEN 再分享群聊回流uv END)
                      + SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv END)
                     ) / (COUNT(DISTINCT mid) + 10) AS 原视频再分享回流率
                    ,(SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv END)
                      + SUM(CASE WHEN 是否原视频 = '否' THEN 再分享单聊回流uv END)
                     ) / (COUNT(DISTINCT mid) + 10) AS 推荐再分享回流率
                    ,((SUM(CASE WHEN 是否原视频 = '是' THEN 再分享群聊回流uv END)
                       + SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv END)
                      ) / (COUNT(DISTINCT mid) + 10)
                     ) / ((SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv END)
                           + SUM(CASE WHEN 是否原视频 = '否' THEN 再分享单聊回流uv END)
                          ) / (COUNT(DISTINCT mid) + 10)
                     ) AS 原视频质量
                    ,SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv ELSE 0 END)
                     + SUM(CASE WHEN 再分享单聊回流uv > 0 THEN 再分享单聊回流uv ELSE 0 END) AS 再分享回流uv
                    ,ROW_NUMBER() OVER (PARTITION BY channel ORDER BY COUNT(DISTINCT mid) DESC) AS rn
            FROM    loghubods.opengid_base_data
            WHERE   dt >= ${start}
            AND     dt <= ${end}
            AND     usersharedepth = 0
            AND     videoid IS NOT NULL
            GROUP BY dt
                     ,channel
                     ,rootsourceid
                     ,wx_sn
                     ,公众号名
                     ,文章标题
                     ,contenturl
                     ,shareid
                     ,分享标题
                     ,分享封面
                     ,videoid
                     ,title
                     ,merge一级品类
                     ,merge二级品类
        ) t
WHERE   rn <= 500
ORDER BY channel, 点击uv DESC
;
