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

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 tp
from tb_user_video_log u
left join tb_video_info v
on u.video_id = v.video_id
left 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_index
from basetable
group by video_id,enddate,currenttime,posttime
having datediff(currenttime,posttime)<30
order by hot_index desc
limit 3