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

bug题，建议自测通过即可，提交如果不加limit筛掉bug行是不可能通过的。

select 
video_id,
round((100*a+
5*b+
3*c+
2*d)/(day_interval+1),0) as hot_index
from(
select 
tb1.video_id, 
avg(timestampdiff(second,start_time,end_time)>=duration) a,
sum(if_like) b,
count(comment_id) c,
sum(if_retweet) d,
datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) as day_interval
from tb_user_video_log tb1 
join tb_video_info tb2 on tb1.video_id=tb2.video_id
where datediff(date((select max(end_time) from tb_user_video_log)),date(release_time))<=29
group by tb1.video_id
) as tb3
ORDER BY hot_index DESC
limit 3