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

select t1.video_id
,round(
((sum(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end ))/count(*)*100
+sum(if_like)*5
+count(comment_id)*3
+sum(if_retweet)*2)
*
1/(DATEDIFF((select date(max(end_time)) from tb_user_video_log),date(max(end_time)))+1)
) as hot_index
from tb_user_video_log t1,tb_video_info t2
where t1.video_id=t2.video_id
and DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)),DATE(release_time)) <= 29
group by t1.video_id
order by hot_index desc
limiT 3