with basetable as(select  u.video_id, u.if_like, u.if_retweet, u.comment_id, max(date(u.end_time))over(partition by u.video_id) as enddate, date(ct.current) as currenttime, date(v.release_time) as posttime, if(timestampdiff(second,u.start_time,u.end_time) >= v.duration,"完成","未完成") as tpfrom tb_user_video_log uleft join tb_video_info von u.video_id = v.video_idleft join (    select max(end_time) as current from tb_user_video_log    ) ct on 1)select video_id,       round((sum(if(tp="完成",1,0))/count(video_id)*100       +sum(if_like)*5       +count(comment_id)*3       +sum(if_retweet)*2)       /(datediff(currenttime,enddate)+1),0) as hot_indexfrom basetablegroup by video_id,enddate,currenttime,posttimehaving datediff(currenttime,posttime)<30order by hot_index desclimit 3