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

with t1 as
(
select 
video_id,
sum(if(timestampdiff(second,start_time,end_time) >= duration,1,0)) as view_all,
count(start_time) as view_amout,
sum(if_like) as like_amout,
count(comment_id) as comment_amout,
sum(if_retweet) as share_amout,
datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) as no_play_day_cnt
from tb_video_info
join tb_user_video_log using(video_id)
where
    datediff (
        date((select max(end_time) from tb_user_video_log)),
        date(release_time)
    ) <= 29
group by 1
)

select 
video_id,
round((100*(view_all/view_amout)+5*like_amout+3*comment_amout+2*share_amout)/(1+no_play_day_cnt)) as hot_index
from t1
order by 2 desc
limit 3