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

SELECT
    video_id,
    round(sum(tp) / count(DISTINCT id), 3) avg_comp_play_rate
FROM
(
        SELECT
            a.id,
            a.video_id,
            a.tt,
            b.duration,
            CASE
                WHEN tt >= b.duration THEN 1
                ELSE 0
            END tp
        FROM
            (
                SELECT
                    id,
                    video_id,
                    UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) tt
                FROM
                    tb_user_video_log
                WHERE
                    year(start_time) = '2021'
            ) a
            LEFT JOIN (
                SELECT
                    id,
                    video_id,
                    duration
                FROM
                    tb_video_info
            ) b ON a.video_id = b.video_id
    ) m1
GROUP BY
    video_id
ORDER BY
    avg_comp_play_rate DESC