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

with t1 as(
select *,date_sub(max_et,interval 29 day) haed_time 
from(
select tuvl.*,tvi.duration,tvi.release_time, 
date(max(end_time) over())as max_et
from tb_user_video_log tuvl
left join tb_video_info tvi using(video_id) 
) tmp 
),
-- 过滤掉非一个月内的数据,并打上是否完播的标签
t2 as(
select *,case when 
TIMESTAMPDIFF(SECOND,start_time,end_time) >=duration 
then 1 else 0 end as wb
from t1 where date(release_time) >= haed_time 
)
-- 按照影片分组求热度
select video_id,
round((100 *(sum(wb) / count(wb)) +
sum(if_like)*5 + count(comment_id)*3+
sum(if_retweet)*2 ) / (1+DATEDIFF(date(max(max_et)),date(max(end_time)))),0) h
from t2 group by video_id order by h desc limit 3