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

SELECT video_id, round((100finishi_rate+5like_num+3comment_num+2retweet_num)(1/(notshow+1)),0) as hot_index FROM ( SELECT v.video_id, sum(case when timestampdiff(second, start_time, end_time)>=duration then 1 else 0 end)/ count() as finishi_rate, sum(if_like) as like_num, sum(if (comment_id is null,0,1)) as comment_num, sum(if_retweet) as retweet_num, DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), MAX(DATE(end_time))) as notshow from tb_video_info v join tb_user_video_log uv using(video_id) where DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) <= 29 group by v.video_id )t1 order by hot_index desc limit 3