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

select a.video_id, round((100*a.vi_cm+5*a.su_like+3*a.su_co+2*a.su_re)*(1/(a.no_play+1))) hot_index
from 
(
    select u.video_id, 
    sum(u.if_like) su_like, 
    sum(u.if_retweet) su_re, 
    count(u.comment_id) su_co, 
    sum(case when timestampdiff(second,u.start_time,u.end_time)>=v.duration then 1 else 0 end)/count(u.video_id) vi_cm,/*各个视频的完播率*/ 
    datediff((select max(end_time) from tb_user_video_log),max(end_time)) no_play /*无播放天数*/
    from tb_user_video_log u join tb_video_info v on u.video_id=v.video_id
    where v.release_time>=(select date_sub((select max(end_time) from tb_user_video_log),interval 29 day)) #满足查找近一个月发布的视频的条件
    group by u.video_id
) a
order by hot_index desc
limit 0,3

区分timestampdiff和datediff，