题解 | #各个视频的平均完播率#
各个视频的平均完播率
http://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select e1.video_id as video_id, round(sum(if(TIMESTAMPDIFF(SECOND, e1.start_time, e1.end_time) >= e2.duration, 1, 0)) / count(e1.start_time), 3) as avg_com_play_rate from tb_user_video_log e1 join tb_video_info e2 on e1.video_id = e2.video_id where DATE_FORMAT(e1.start_time, '%Y') = 2021 group by e1.video_id order by avg_com_play_rate desc
