select video_id,
round((100*watch_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(no_watch_cnt+1)) as hot_index
from
    (select log.video_id,
    avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) as watch_rate,
    sum(if_like) as like_cnt,
    count(comment_id) as comment_cnt,
    sum(if_retweet) as retweet_cnt,
    datediff(max(now_date),max(end_time)) as no_watch_cnt
    from tb_user_video_log as log
    left join 
    (select max(end_time) as now_date from tb_user_video_log
    )as t1 on 1
    left join tb_video_info as info on log.video_id=info.video_id
    where datediff(now_date,release_time)<30
    group by log.video_id
     ) as t2
 order by hot_index desc
 limit 3