题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
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