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

with a as(select MAX(DATE(end_time)) rt
from tb_user_video_log t1 left join tb_video_info t2 on t1.video_id=t2.video_id
), 
b as(select t1.video_id,start_time,end_time,if_like	,if_retweet	,comment_id	,duration
from tb_user_video_log t1 left join tb_video_info t2 on t1.video_id=t2.video_id
where timestampdiff(day,date(release_time),(select rt from a))<=29),
c as(select video_id,AVG(IF(TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0)) as comp_play_rate,SUM(if_like) as like_cnt,COUNT(comment_id) as comment_cnt,SUM(if_retweet) as retweet_cnt,MAX(DATE(end_time)) as recently_end_date
from b
group by video_id)
select video_id,round((100 * comp_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt)/(timestampdiff(day,recently_end_date,(select rt from a))+1),0) hot
from c
order by hot desc
limit 3