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

select 
    video_id,
    round(
            (100*(sum(if_wanbo)/count(*))+5*sum(if_like)+3*sum(if_comment)+2*sum(if_retweet))/(1+min(diff_time))
        ) as hot_index
from
    (
        select 
            tb_user_video_log.video_id,
            if(timestampdiff(second,start_time,end_time)>=duration,1,0) as if_wanbo,
            if_like,
            if(comment_id is null,0,1) as if_comment,
            if_retweet,
            DATEDIFF((select max(end_time) from tb_user_video_log),end_time) as diff_time
        from 
            tb_user_video_log 
        join 
            tb_video_info 
        on 
            tb_user_video_log.video_id = tb_video_info.video_id
        where 
            DATEDIFF((select max(end_time) from tb_user_video_log),release_time) < 30
    ) as t
group by 1
order by 2 desc 
limit 3;