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

with t as(
    select
        i.video_id,
        avg(timestampdiff(second,start_time,end_time)>=i.duration) full_play_rate,
        sum(if_like) like_cnt,
        count(comment_id) comment_cnt,
        sum(if_retweet) retweet_cnt,
        datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) nonplayed_days
    from tb_video_info i join tb_user_video_log p using(video_id)
    where datediff(date((select max(end_time) from tb_user_video_log)),date(release_time))<=29
    group by 1)

select
    video_id,
    round((100*full_play_rate
        +5*like_cnt
        +3*comment_cnt
        +2*retweet_cnt)/(nonplayed_days+1)) hot_index
from t
order by 2 desc
limit 3