题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
首先计算热度所需要的各个小的部分内容，主要是新鲜度，每个视频的最近无播放天数用整个的最近播放的日期与各个视频最近一次播放日期之差来表示
为了方便简历临时表 然后再按照公式计算即可。
with t1 as (select a.video_id video_id,release_time,max(end_time) recent_play_day,
sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(a.video_id) as play_rate,sum(if_like) as like_cnt,sum(if_retweet) as ret_cnt,
count(comment_id) com_cnt,
datediff(date((select max(end_time) from tb_user_video_log)),date(max(end_time))) as noplay_day_cnt
from tb_user_video_log a
join tb_video_info b
on a.video_id = b.video_id
where datediff(date((select max(end_time) from tb_user_video_log)),date(release_time)) < 30
group by a.video_id)
select video_id,round((100*play_rate+5*like_cnt+3*com_cnt+2*ret_cnt)*(1/(noplay_day_cnt+1)),0) hot_index
from t1
order by hot_index desc
limit 3
投递拼多多集团-PDD等公司10个岗位