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

SELECT 
    video_id, 
    ROUND(SUM(finish)/COUNT(finish),3) AS avg_comp_play_rate
FROM (
    SELECT 
        video_id, 
        IF (end_time - start_time >= duration, 1 ,0) AS finish
    FROM tb_user_video_log 
    JOIN tb_video_info USING (video_id)
    WHERE YEAR(start_time) = 2021 AND YEAR(end_time) = 2021
) AS finish_table
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC;