题解 | #各个视频的平均完播率#
各个视频的平均完播率
http://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
SELECT a.video_id,
round(sum((end_time-start_time-b.duration) >=0 )/count(a.video_id),3) avg_comp_play_rate
FROM tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
where start_time like '2021%'
GROUP BY a.video_id
order by avg_comp_play_rate desc
