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

题目难度不大，但是细节很多，一定要仔细阅读文章要求。

select video_id,
  round((100*OVER_+5*like_+3*com+2*retweet)/(days+1),0) as hot_index
from
(
    select vid.video_id,sum(if(TIMESTAMPDIFF(SECOND,start_time,end_time)
              >=duration,1,0))/count(us.id) as OVER_,
       sum(if(if_like=1,1,0)) as like_,
       count(comment_id) as com,
       sum(if(if_retweet=1,1,0)) as retweet,
       timestampdiff(day,max(DATE_FORMAT(end_time,"%Y-%m-%d")),
                            (select max(DATE_FORMAT(end_time,"%Y-%m-%d"))
                              from tb_user_video_log)) as days
from tb_user_video_log us LEFT JOIN tb_video_info vid
    using (video_id)
where release_time BETWEEN TIMESTAMPADD(DAY,-29,(select max(DATE_FORMAT(end_time,"%Y-%m-%d"))
                              from tb_user_video_log)) and
                              (select max(DATE_FORMAT(end_time,"%Y-%m-%d"))
                              from tb_user_video_log)
group by vid.video_id
) base
order by hot_index desc
limit 3