题解 | #各个视频的平均完播率#
各个视频的平均完播率
http://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
SELECT
tb.video_id,
ROUND(SUM(IF(
TIME_TO_SEC(end_time)-TIME_TO_SEC(start_time) >= info.duration,
1,
0
))/COUNT(tb.video_id),3)AS avg_comp_play_rate
FROM tb_user_video_log tb
JOIN tb_video_info info USING(video_id)
WHERE YEAR(start_time)='2021'
GROUP BY tb.video_id
ORDER BY avg_comp_play_rate DESC
