题解 | #各个视频的平均完播率#

各个视频的平均完播率

https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753

暴力解题法:
1. 先取出所有需要的字段
select start_time, uid, t.video_id, duration,
    case when (end_time - start_time) >= duration the 1
      else 0 end as if_all_played
from tb_user_video_log t
left join tb_video_info v
on t.video_id = v.video_id;
2. 根据取出数据计算,并按照题目要求筛选
select tt.video_id, round((sum(tt.if_all_played) / count(tt.video_id)), 3) as avg_comp_play_rate
from (
   select start_time, uid, t.video_id, duration,
       case when (end_time - start_time) >= duration then 1
       else 0 end as if_all_played
   from tb_user_video_log t
   left join tb_video_info v on t.video_id = v.video_id ) tt
where year(start_time) = 2021
group by video_id
order by avg_comp_play_rate desc;

全部评论

相关推荐

点赞 评论 收藏
分享
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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