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

with now_t as (
    select date(max(end_time)) as now_date
    from tb_user_video_log
),
numbers_t as(
    select 
        l.video_id,
        max(date(end_time)) as end_day,
        sum(if_like) as likes,
        sum(if(comment_id is not null,1,0)) as comments,
        sum(if_retweet) as retweets,
        sum(if(timestampdiff (second , start_time , end_time)>=duration,1,0)) as playover,
        sum(if(timestampdiff (second , start_time , end_time)>=duration,0,1)) as unplayover
    from  tb_video_info i
        join tb_user_video_log l using(video_id)
        join now_t
    where timestampdiff(day,date(release_time),now_date)<=29
    group by l.video_id
)
select 
    video_id,
    round((playover/(playover+unplayover)*100+likes*5+comments*3 +retweets*2)/(timestampdiff(day,end_day,now_date)+1),0) as hot_index
from numbers_t join now_t
group by video_id,now_date
order by hot_index desc
limit 3;