题解 | 近一个月发布的视频中热度最高的top3视频
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
with t as(
select
i.video_id,
avg(timestampdiff(second,start_time,end_time)>=i.duration) full_play_rate,
sum(if_like) like_cnt,
count(comment_id) comment_cnt,
sum(if_retweet) retweet_cnt,
datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) nonplayed_days
from tb_video_info i join tb_user_video_log p using(video_id)
where datediff(date((select max(end_time) from tb_user_video_log)),date(release_time))<=29
group by 1)
select
video_id,
round((100*full_play_rate
+5*like_cnt
+3*comment_cnt
+2*retweet_cnt)/(nonplayed_days+1)) hot_index
from t
order by 2 desc
limit 3
字节跳动公司福利 1371人发布