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

select video_id,round((100*a+5*b+3*c+2*d)*1/(e+1),0) hot_index
from
    (
    select a1.video_id,sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(start_time) a,sum(if_like) b,count(comment_id) c,sum(if_retweet) d,datediff((select max(end_time) from tb_user_video_log),max(end_time)) e
    from tb_user_video_log a1
    left join tb_video_info a2
    on a1.video_id=a2.video_id
    where datediff((select max(end_time) from tb_user_video_log),release_time)<30    
    group by a1.video_id
    ) aa
order by hot_index desc limit 3