题解 | 近一个月发布的视频中热度最高的top3视频

WITH
-- 第一步：动态计算全局最近播放日期T
global_T AS (
    SELECT MAX(DATE(end_time)) AS T FROM tb_user_video_log
),
latest_info AS (
    SELECT
        tvi.video_id,
        COUNT(tuvi.start_time) AS play_cnt,
        SUM(CASE WHEN TIMESTAMPDIFF(SECOND, tuvi.start_time, tuvi.end_time) >= tvi.duration THEN 1 ELSE 0 END) AS finish_video_cnt,
        SUM(tuvi.if_like) AS like_cnt,
        COUNT(tuvi.comment_id) AS comment_cnt,
        SUM(tuvi.if_retweet) AS retweet_cnt,
        -- 直接计算新鲜度：T - 最后播放日期
        DATEDIFF((SELECT T FROM global_T), MAX(DATE(tuvi.end_time))) AS fresh_rate
    FROM tb_video_info tvi
    LEFT JOIN tb_user_video_log tuvi 
        ON tvi.video_id = tuvi.video_id
        -- 过滤播放时间
        AND DATE(tuvi.end_time) BETWEEN DATE_SUB((SELECT T FROM global_T), INTERVAL 29 DAY) AND (SELECT T FROM global_T)
    -- 核心：筛选近一个月发布的视频
    WHERE tvi.release_time BETWEEN DATE_SUB((SELECT T FROM global_T), INTERVAL 29 DAY) AND (SELECT T FROM global_T)
    GROUP BY tvi.video_id
    HAVING play_cnt > 0
)
SELECT
    video_id,
    ROUND((100*(finish_video_cnt/play_cnt) + 5*like_cnt + 3*comment_cnt + 2*retweet_cnt) / (fresh_rate + 1), 0) AS hot
FROM latest_info
ORDER BY hot DESC
LIMIT 3;