题解 | #近一个月发布的视频中热度最高的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






全部评论

相关推荐

06-12 16:23
已编辑
长安大学 C++
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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