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

SELECT
    table1.video_id,
    ROUND(
        COUNT(
            CASE
                WHEN table1.last >= table1.duration THEN 1
                ELSE null
            END
        ) / COUNT(*),
        3
    ) AS avg_comp_play_rate
FROM
    (
        SELECT
            video_time.video_id AS video_id,
            last,
            duration
        FROM
            (
                SELECT
                    video_id,
                    end_time - start_time AS last
                FROM
                    tb_user_video_log
                WHERE YEAR(start_time) = 2021
            ) AS video_time
            INNER JOIN tb_video_info ON video_time.video_id = tb_video_info.video_id
    ) AS table1
GROUP BY table1.video_id
ORDER BY avg_comp_play_rate DESC;