题解 | #各个视频的平均完播率#
各个视频的平均完播率
http://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
/*
* 完播率：完整播放的次数 / 总播放次数
*/
SELECT video_id,
ROUND(COUNT(IF(play_time >= duration, video_id, NULL)) / COUNT(video_id), 3) AS avg_comp_play_rate
FROM (
-- 筛选2021年有播放记录的视频和播放时长
SELECT video_id,
TIMESTAMPDIFF(SECOND, start_time, end_time) AS play_time
FROM tb_user_video_log
WHERE YEAR(start_time) = '2021'
) play_time_t LEFT JOIN tb_video_info USING(video_id)
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC
查看3道真题和解析