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

-- 查询最新日期
with t1 as (select max(date(end_time)) as latest_date from tb_user_video_log),

-- 查询近一个月发布的视频
t2 as (select video_id, duration from tb_video_info 
where date(release_time) >= date_sub((select latest_date from t1), interval 29 day)),

-- 整理互动表
t3 as (select log.video_id, log.end_time,
case when timestampdiff(second, start_time, end_time) >= t2.duration then 1 else 0 end as if_finish, if_like, if_retweet, 
case when comment_id is not null then 1 else 0 end as if_comment 
from 
tb_user_video_log log inner join t2 on log.video_id = t2.video_id),

t4 as (select video_id, sum(if_finish)/ count(*) as finish_rate, sum(if_like) as num_like,
sum(if_comment) as num_comment, sum(if_retweet) as num_retweet,
datediff((select latest_date from t1), max(date(end_time))) 
as num_nodays
from t3
group by video_id)

select video_id, round((100 * finish_rate + 5 * num_like + 3 * num_comment + 2 * num_retweet) 
/ (num_nodays + 1),0) as hot_index from t4 
order by hot_index desc limit 3