-- 渠道用户量统计
-- 按人数（UV）统计各渠道及素材字段覆盖情况

SELECT  channel
        ,COUNT(DISTINCT mid) AS 用户数
        ,COUNT(*) AS 点击数
        ,COUNT(DISTINCT CASE WHEN 文章标题 IS NOT NULL AND 文章标题 != '' THEN mid END) AS 有文章标题用户
        ,COUNT(DISTINCT CASE WHEN 分享标题 IS NOT NULL AND 分享标题 != '' THEN mid END) AS 有卡片标题用户
        ,COUNT(DISTINCT CASE WHEN 分享封面 IS NOT NULL AND 分享封面 != '' THEN mid END) AS 有卡片封面用户
FROM    loghubods.opengid_base_data
WHERE   dt >= ${start}
AND     dt <= ${end}
AND     usersharedepth = 0
AND     videoid IS NOT NULL
GROUP BY channel
ORDER BY 用户数 DESC
;
