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

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