题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
SELECT t1.video_id, ROUND(SUM(t1.end_time-t1.start_time >= duration)/COUNT(*),3) AS avg_comp_play_rate FROM tb_user_video_log t1 INNER JOIN tb_video_info t2 USING(video_id) WHERE year(start_time) = 2021 GROUP BY t1.video_id ORDER BY avg_comp_play_rate DESC;
完成播放 => t1.end_time-t1.start_time >= duration
