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

一定要细心，题目中问的是2021年的视频播放

SELECT a.video_id , 
		ROUND(AVG(IF(TIMESTAMPDIFF(second, start_time, end_time) >= b.duration, 1, 0)),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 YEAR(start_time) = 2021
GROUP BY a.video_id
ORDER BY avg_comp_play_rate desc;