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

select distinct t1.video_id,round((100*(play_over_times/play_times) + 5*like_num+3*comment_num+2*retweet_num)/(not_play_days+1),0) as "hot_index"
from(
select tu.video_id, count(1) as "play_times",sum(case when timestampdiff(second,start_time, end_time) >= tv.duration then 1
                                   else 0 end
                                 ) as "play_over_times", sum(if_like) as "like_num", 
                                    sum(
                                    case when comment_id is not null then 1
                                    else 0 end) as "comment_num", sum(if_retweet) as "retweet_num",
                                                            datediff((
                                                            select max(date(end_time))
                                                            from tb_user_video_log
                                                            ), max(date(end_time))) as "not_play_days"
from tb_user_video_log tu join tb_video_info tv
on tu.video_id = tv.video_id
where datediff((select max(end_time) from tb_user_video_log), release_time)<=29 
group by tu.video_id
)t1
order by hot_index desc
limit 0,3