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

/*
* 完播率：完整播放的次数 / 总播放次数
*/

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