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

# 1. 近一个月发布的视频
# 2. 热度最高， 热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度
-- 新鲜度=1/(最近无播放天数+1)；a,b,c,d分别为100、5、3、2
# 最近播放日期以end_time-结束观看时间为准
# 热度保留为整数，并按热度降序排序。
SELECT
    video_id,
    ROUND(rd * 1/(no_play_days + 1), 0) AS hot_index
FROM(
    SELECT
        video_id,
        DATEDIFF((SELECT MAX(DATE(end_time)) FROM tb_user_video_log), end_day) AS no_play_days, # 最近无播放天数
        100 * fr + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt AS rd
    FROM(
        SELECT
            tu.video_id,
            MAX(DATE(end_time)) AS end_day, # 最近播放日期
            SUM(if_like) AS like_cnt, # 点赞数
            COUNT(comment_id) AS comment_cnt, # 评论数
            SUM(if_retweet) AS retweet_cnt, # 转发数
            SUM(IF(TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration, 1, 0)) / COUNT(end_time) AS fr
        FROM tb_user_video_log tu # 完播率
        JOIN tb_video_info tv
        ON tu.video_id = tv.video_id
        WHERE DATEDIFF((SELECT MAX(DATE(end_time)) FROM tb_user_video_log), DATE(release_time)) <= 29 # 近一月发布
        GROUP BY tu.video_id
    ) AS tb1
) AS tb2
ORDER BY hot_index DESC # 条件热度降序排序
LIMIT 3 # top3