题解 | #各个视频的平均完播率#
各个视频的平均完播率
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;