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

with a1 as(select video_id
,sum(finish_show_cnt)/count(*) finish_rate
,sum(if_like) sum_like
,count(comment_id) sum_comment
,sum(if_retweet) sum_retweet
,any_value(min_day_gap) min_day_gap
from (
select video_id,finish_show_cnt,if_like,comment_id,if_retweet
,min(datediff(recent_date,date))over(partition by video_id) min_day_gap 
from (
select t1.video_id,date(end_time) date,release_time
,case when (timestampdiff(second,start_time,end_time)/duration) >=1 then 1 else 0 end finish_show_cnt
,if_like,comment_id,if_retweet 
,max(date(end_time))over(order by video_id) recent_date
from tb_user_video_log t1
left join tb_video_info t2 
on t1.video_id = t2.video_id) a
where datediff(recent_date,release_time) <= 29) b
group by video_id)
select video_id
,round((100*finish_rate+5*sum_like+3*sum_comment+2*sum_retweet)/(min_day_gap + 1),0) hot_index
from a1
order by hot_index desc
limit 3;