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

with t as(
    select 
        t1.video_id,
        avg(case when timestampdiff(second,start_time,end_time)>=duration then 1
            else 0 end ) as a,
        sum(if_like) as b,
        count(comment_id) as c,
        sum(if_retweet) as d,
        DATEDIFF((select date(max(end_time)) from tb_user_video_log),date(max(end_time))) as e
    from tb_user_video_log t1
    left join tb_video_info t2
        on t1.video_id=t2.video_id
    where datediff ((select date(max(end_time)) from tb_user_video_log),date(release_time))<=29
    group by video_id
)
select video_id,round((100*a+5*b+3*c+2*d)/(e+1)) as hot_index
from t
order by hot_index desc
limit 3