SQL笔记7：近一个月发布的视频中热度最高的top3视频

---

前言

题目来源于牛客网sql企业题库第6题（困难）：
近一个月发布的视频中热度最高的top3视频

问题：

代码如下：

1）需要筛选数据，按照近一个月内：

//作为where条件
datediff(date((select max(end_time)from tb_user_video_log)), date(t2.release_time))<=29

2）以video_id为group by条件，计算点赞数、转发数、评论数、视频完播率

//点赞数
sum(if_like) likes
//转发数
sum(if_retweet) retweets
//评论数
count(comment_id) comments
//视频完播率
avg(case when timestampdiff(second, start_time, end_time)>=duration then 1 else 0 end) play_rate
//最近无播放天数
datediff((select max(end_time) from tb_user_video_log), max(t1.end_time)) day_new

注意两个用法：
timestampdiff(second,start_time,end_time)：返回两个datetime类型数据之间相差的秒数
datediff(a,b)：a，b为sql语句时需要加括号

总代码如下：

//代码来源牛客题解区大佬,自己写的又臭又长
select
    a.video_id,
    round((100*play_rate + 5*likes + 3*comments + 2*retweets)*(1/(day_new+1)),0) hot_index
from
    (select
         t1.video_id, 
         avg(case when timestampdiff(second, start_time, end_time)>=duration then 1 else 0 end) play_rate,
         sum(if_like) likes,
         count(comment_id) comments,
         sum(if_retweet) retweets,
         datediff((select max(end_time) from tb_user_video_log), max(t1.end_time)) day_new 
     from
         tb_user_video_log t1
     join
         tb_video_info t2
     on
         t1.video_id=t2.video_id
     where
         datediff(date((select max(end_time)from tb_user_video_log)), date(t2.release_time))<=29
     group by
         t1.video_id
    ) a
ORDER BY
    hot_index DESC
LIMIT
    3

easy ~~