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

with t1 as(
SELECT
	video_id,if_follow,if_like,if_retweet,start_time,end_time,release_time,
CASE WHEN timestampdiff( SECOND, start_time, end_time )>= duration then 1 ELSE 0 
	END AS if_finish,
	max(DATE(end_time))over() as max_date,
	max(DATE(end_time))over(partition by video_id)as max_date_group,
	case when comment_id is NULL then 0 else 1 end as if_comment
FROM
	tb_user_video_log
	JOIN tb_video_info USING (
	video_id)
	),
	t2 as (select *,DATEDIFF(max_date,max_date_group) as no_play_day from t1 
	where DATEDIFF(max_date,release_time)<30)


	
select video_id,
round((sum(if_finish)/count(*)*100+5*sum(if_like)+2*sum(if_retweet)+3*sum(if_comment))/(max(no_play_day)+1),0)  as hot_index
from t2 group by video_id
order by hot_index desc

limit 3