题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
SELECT video_id,ROUND(AVG(IF(
TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0
)),3) as avg_comp_play_rate
FROM tb_user_video_log
JOIN tb_video_info USING(video_id)
WHERE YEAR(start_time)=2021
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC;