题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
with a1 as ( select Max(date(end_time)) as end_time from tb_user_video_log ), a2 as ( select tb_user_video_log.video_id, sum(case when timestampdiff(second, start_time, end_time)>= duration then 1 else 0 end)/count(*) as completerate, sum(if_like) as likecount, count(comment_id) as commentcount, sum(if_retweet) as if_retweetcount, timestampdiff(day, max(date(end_time)), (select end_time from a1)) as fresh from tb_user_video_log left join tb_video_info on tb_user_video_log.video_id = tb_video_info.video_id where date(release_time) >=date_sub((select end_time from a1), interval 29 day) and date(release_time) <= (select end_time from a1) group by tb_user_video_log.video_id ), a3 as ( select video_id, round((100*completerate+5*likecount + 3*commentcount + 2*if_retweetcount)/(fresh+1), 0) as hot_index from a2 ) select video_id, hot_index from a3 order by hot_index desc limit 3