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

每个城市中评分最高的司机信息

http://www.nowcoder.com/practice/dcc4adafd0fe41b5b2fc03ad6a4ac686

select video_id, 
round((100 * comp_play_rate + 5 * num_like + 3 * num_comment + 2 * num_retweet)
      * fresh_rate, 0) as hot_index
from(
    select V.video_id,
    1 / (datediff((select max(end_time) from tb_user_video_log), max(U.end_time)) + 1) as fresh_rate,
    avg(if(timestampdiff(second, U.start_time, U.end_time) >= duration, 1, 0)) as comp_play_rate,
    # U.end_time - U.start_time 会报错
    sum(if_like) as num_like,
    count(comment_id) as num_comment,
    sum(if_retweet) as num_retweet
    from tb_user_video_log as U
    left join tb_video_info as V
    on U.video_id = V.video_id
    where datediff((select max(end_time) from tb_user_video_log), V.release_time) <= 29
    group by V.video_id
) as play_info
order by hot_index desc
limit 3
select video_id,
        round((100 * comp_play_rate + 5 * num_like + 3 * num_comment + 2 * num_retweet)
              * fresh_rate, 0) as hot_index
from
(
select video_id,
        1/(DATEDIFF((select max(end_time) from tb_user_video_log), max(end_time))+1),0) as fresh_rate,
        avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) as comp_play_rate,
        sum(if_like) as num_like,
        count(comment_id) as num_comment,
        sum(if_retweet) as num_retweet
from tb_user_video_log uv join tb_video_info vi using(video_id)
where datediff(date(select max(end_time)),release_time)<=29
group by tag
) a
order by hot_index desc
limit 3

全部评论

相关推荐

重生我想学测开:嵌入式的问题,我准备入行京东外卖了
点赞 评论 收藏
分享
05-30 18:54
武汉商学院 Java
湫湫湫不会java:先投着吧,大概率找不到实习,没实习的时候再加个项目,然后把个人评价和荣誉奖项删了,赶紧成为八股战神吧,没实习没学历,秋招机会估计不多,把握机会。或者说秋招时间去冲实习,春招冲offer,但是压力会比较大
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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