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

SELECT
    video_id,
    ROUND((100 * full_play_rate + 5 * like_cnt+ 3 * comment_cnt + 2 * retweet_cnt) / (no_play_cnt + 1), 0) AS hot_index
FROM(
    SELECT
        video_id,
        AVG(timestampdiff(second, start_time, end_time) >= duration) AS full_play_rate,
        SUM(if_like) AS like_cnt,
        COUNT(comment_id) AS comment_cnt,
        SUM(if_retweet) AS retweet_cnt,
        DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE((MAX(end_time)))) AS no_play_cnt
    FROM
        tb_user_video_log
    JOIN 
        tb_video_info USING(video_id)
    WHERE
        DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) < 30
    GROUP BY
        video_id
) temp
ORDER BY 
    hot_index DESC
LIMIT
    3