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

SELECT  
    video_id,
    ROUND(SUM(is_wanbo)/COUNT(is_wanbo), 3) AS avg_comp_play_rate
FROM (
    SELECT
        tb_user_video_log.video_id,
        CASE WHEN TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration THEN 1
        ELSE 0
        END AS is_wanbo
    FROM tb_user_video_log JOIN tb_video_info
    ON tb_user_video_log.video_id = tb_video_info.video_id
    WHERE YEAR(start_time) = 2021 ) t
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC