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

select video_id,
       round((100*already_rate + 5*like_num + 3*comment_num + 2*retweet_num)/(1+last_no_day))  as hot_index
from(
    select a.video_id as video_id,
            sum(if(watch_time>=duration,1,0))/count(*) as already_rate,
            sum(if_like) as like_num,
            sum(if_comment) as comment_num,
            sum(if_retweet) as retweet_num,
            any_value(datediff(now_dt,max(dt))) as last_no_day #最近无播放天数
    from
        (select video_id, 
                timestampdiff(second,start_time,end_time) as watch_time,
                date(end_time) as dt, 
                if_like, if_retweet,
                if(isnull(comment_id),0,1) as if_comment,
                max(date(end_time)) over() as now_dt
        from tb_user_video_log) as a
        left join tb_video_info b using(video_id)

    where datediff(now_dt,date(release_time)) between 0 and 29 #近一个月
    group by video_id
) as t2
order by hot_index desc
limit 3;