题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
SELECT v.video_id,
ROUND(IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(SECOND, u.start_time, u.end_time) >= v.duration THEN 1 ELSE 0 END) / COUNT(*), 0), 3) AS avg_comp_play_rate
FROM tb_user_video_log u
JOIN tb_video_info v ON u.video_id = v.video_id
WHERE YEAR(u.start_time) = 2021
GROUP BY v.video_id
ORDER BY avg_comp_play_rate DESC
海康威视公司福利 1086人发布