题解 | #近一个月发布的视频中热度最高的top3视频#
拆解重组是解答所有复杂题目的核心，以不变应万变
select video_id,round((100*(sum(play)/count(play))+5*sum(if_like)+3*sum(if(comment_id is null,0,1))+2*sum(if_retweet))/(avg(no_play)+1),0) hot_index from
(select video_id,case when timestampdiff(second,start_time,end_time) >= duration then 1 else 0 end play,if_like,comment_id,if_retweet,datediff(max(date(end_time))over(),max(date(end_time))over(partition by video_id)) no_play from tb_user_video_log join tb_video_info using(video_id)
where date(release_time) >= (select date_sub(max(date(end_time)),interval 29 day) from tb_user_video_log)) t1
group by video_id
order by hot_index desc
limit 3
