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

select 
    video_id,
    round((100*wb_rt+5*lcnt+3*ccnt+2*recnt)/(1+npcnt),0) as hot_index
from(
    select
        a.video_id,
        sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(1) as wb_rt,
        sum(if_like) as lcnt,
        sum(if_retweet) as recnt,
        count(comment_id) as ccnt,
        datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) as npcnt
    from 
        tb_user_video_log as a
    join 
        tb_video_info as b
    on a.video_id =b.video_id
    and datediff(date((select max(end_time) from tb_user_video_log)),date(release_time)) <= 29
    group by 1) as t
order by hot_index desc
limit 3