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

SELECT
    video_id,
    ROUND((100 * finished_rate +
           5 * like_cnt +
           3 * comment_cnt +
           2 * retweet_cnt) / (no_play_record_day_cnt + 1), 0) hot_index from
(select video_id, SUM(TIMESTAMPDIFF(second, start_time, end_time) >= duration) / COUNT(*) finished_rate,
sum(if_like) like_cnt,
sum(if(comment_id is null,0,1)) comment_cnt,
sum(if_retweet) retweet_cnt,
datediff(date((select max(end_time)
from tb_user_video_log)) , max(date(end_time))) no_play_record_day_cnt
FROM tb_video_info i
        JOIN tb_user_video_log USING(video_id)
WHERE DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) <= 29
        GROUP BY video_id) t
ORDER BY 2 DESC
LIMIT 3

汇总类型的题目中很棒的一个