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

• 使用COUNT()函数时，注意值为NULL和0的区别。值为NULL时不会被算入内，但为0时会被count()纳入计算。
• 根据解释，最近无播放天数应该视频的最近播放日期和全部视频中最近播放天数之差

SELECT video_id,
    ROUND((100 * play_rate 
           + 5 * like_cnt 
           + 3 * comment_cnt 
           + 2 * retweet_cnt) / fresh_index, 0) AS hot_index
FROM (
    SELECT video_id, 
        COUNT(IF(TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration, video_id, NULL)) / COUNT(end_time) AS play_rate,
        SUM(if_like) AS like_cnt,
        COUNT(comment_id) AS comment_cnt,
        SUM(if_retweet) AS retweet_cnt,
        DATEDIFF((SELECT MAX(DATE(end_time)) FROM tb_user_video_log), DATE(MAX(end_time))) + 1 AS fresh_index
    FROM tb_user_video_log
        JOIN tb_video_info
        USING(video_id)
    WHERE DATEDIFF((SELECT MAX(DATE(end_time)) FROM tb_user_video_log), DATE(release_time)) <= 29
    GROUP BY video_id
)cnt_table
ORDER BY hot_index DESC
LIMIT 3