题解 | 近一个月发布的视频中热度最高的top3视频
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
select
video_id,
round((100*完播率+5*点赞数+3*评论数+2*转发数)*(1/(1+最近无播放天数)),0) as hot_index
from
(
select
tb_user_video_log.video_id video_id
,AVG(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) as 完播率
,sum(if_like) as 点赞数
,count(comment_id) as 评论数
,sum(if_retweet) as 转发数
,datediff((select max(end_time) from tb_user_video_log),max(end_time)) as 最近无播放天数
from tb_user_video_log
inner join tb_video_info
on tb_user_video_log.video_id = tb_video_info.video_id
where datediff((select date(max(end_time)) from tb_user_video_log),release_time)<=29
group by tb_user_video_log.video_id
) as mid_t
order by
hot_index desc
limit 3;
