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

SELECT video_id, ROUND(SUM(CASE WHEN visit_time >= duration THEN 1 ELSE 0 END)/COUNT(uid),3) AS avg_comp_play_rate
FROM(
SELECT uid, t1.video_id, timestampdiff(SECOND, start_time, end_time) AS visit_time, duration
FROM tb_user_video_log t1
JOIN tb_video_info t2 ON t1.video_id=t2.video_id
WHERE YEAR(start_time)=2021
) sub
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC