select video_id,
round(((finished_rate*100+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(no_play_record_day_cnt+1)),0) as hot_index
from (select video_id,
sum((case when TIMESTAMPDIFF(second, start_time, end_time)>=duration then 1 else 0 end))/count(video_id) as finished_rate,
sum(if_like) as like_cnt,
count(comment_id) as comment_cnt,
sum(if_retweet) as retweet_cnt,
DATEDIFF((SELECT MAX(end_time) FROM tb_user_video_log), MAX(end_time)) as no_play_record_day_cnt
from tb_user_video_log
join tb_video_info using(video_id)
where DATEDIFF((select max(end_time) from tb_user_video_log),release_time)<=29
group by video_id) a
order by hot_index desc
limit 3