⭐题解 | 输出播放量最高的视频

WITH step1 AS (
    SELECT 
        a.cid,
        a.id,
        -- 核心：统计 a 的开始时间，被多少个人的观看区间包含
        SUM(IF(a.start_time BETWEEN b.start_time AND b.end_time, 1, 0)) AS cnt
    FROM play_record_tb a
    LEFT JOIN play_record_tb b 
        ON a.cid = b.cid  -- 只和同一个视频的记录匹配
    GROUP BY a.id, a.cid
),
step2 AS (
    SELECT 
        cid,
        MAX(cnt) AS max_peak_uv  -- 每个视频最大同时在线人数
    FROM step1
    GROUP BY cid
)
SELECT 
    cid,
    ROUND(CAST(max_peak_uv AS FLOAT), 3) AS max_peak_uv
FROM step2
ORDER BY max_peak_uv DESC
LIMIT 3;

这个题考察的是同一时刻在线的最大人数，好复杂，得多看几遍