题解 | #近一个月发布的视频中热度最高的top3视频#

近一个月发布的视频中热度最高的top3视频

https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff

# 2. 求和 group by video_id
select video_id, round((100*sum(complete)/count(complete) + 5*sum(if_like) +
3*sum(comment_id)+ 2*sum(if_retweet))*(1/(datediff((select max(date_format(end_time,"%Y-%m-%d")) from tb_user_video_log), max(dt))+1)),0) as hot_index
from(
# 1. join,计算每个视频每天的完播数,点赞数,评论数,转发数
select tb_user_video_log.video_id, date_format(end_time,"%Y-%m-%d") as dt, if_like,
if(comment_id,1,0) as comment_id, if_retweet, case 
when round(timestampdiff(second,start_time,end_time)/duration,2)>=1 then 1
else 0
end as complete
from tb_user_video_log
left join tb_video_info
on tb_video_info.video_id = tb_user_video_log.video_id
where datediff((select max(date_format(end_time,"%Y-%m-%d")) from tb_user_video_log), release_time)< 30
) t
group by video_id
order by hot_index desc
limit 3

全部评论

相关推荐

06-02 15:53
阳光学院 Java
点赞 评论 收藏
分享
程序员小白条:你是沟通了900个,不是投了900份简历,你能投900份,意味着对面都要回复你900次,你早就找到实习了,没亮点就是这样的,别局限地区,时间投的也要早,现在都要7月了
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务