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

select
video_id,
round((100*finish+
5*cnt_like+
3*cnt_comt+
2*cnt_ret)*fresh,0) as hot_index 
from 
(select
    video_id, 
    avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) as finish,
    sum(if_like) as cnt_like,
    sum(if(comment_id,1,0)) as cnt_comt,
    sum(if_retweet) as cnt_ret,
    1/(datediff((select max(date(end_time)) from tb_user_video_log),max(date(end_time)))+1) as fresh
    from tb_user_video_log
    left join tb_video_info using(video_id)
    WHERE DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) <= 29
    group by video_id
)a
order by hot_index desc
limit 3