题解 | 每类视频近一个月的转发量/率

WITH latest_date AS (
    SELECT MAX(start_time) AS max_date
    FROM tb_user_video_log
),
play_info AS (
    SELECT 
        u.video_id,
        u.start_time,
        u.if_retweet,
        v.tag,
        CASE WHEN TIMESTAMPDIFF(DAY, start_time, (SELECT max_date FROM latest_date)) < 30 THEN 1 ELSE 0 END AS recent_play -- 标记近三十天的播放
    FROM tb_user_video_log u
    LEFT JOIN tb_video_info v
    ON u.video_id = v.video_id
)

SELECT 
    tag,
    SUM(if_retweet) AS retweet_cnt,
    ROUND(SUM(if_retweet) / SUM(recent_play), 3) AS retweet_rate
FROM play_info
WHERE TIMESTAMPDIFF(DAY, start_time, (SELECT max_date FROM latest_date)) < 30
-- 一定要加这个条件，不然SUM(if_retweet)计算的是全部时间的转发数！！
GROUP BY tag
ORDER BY retweet_rate DESC