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

WITH time_points AS (
    -- 收集所有可能的关键时间点
    SELECT DISTINCT cid, start_time as check_time 
    FROM play_record_tb
    UNION
    SELECT DISTINCT cid, end_time as check_time 
    FROM play_record_tb
),
concurrent_at_each_time AS (
    -- 对每个时间点计算该时刻的并发数
    SELECT 
        tp.cid,
        tp.check_time,
        COUNT(pr.id) as concurrent_count
    FROM time_points tp
    LEFT JOIN play_record_tb pr ON tp.cid = pr.cid
        AND pr.start_time <= tp.check_time 
        AND pr.end_time >= tp.check_time
    GROUP BY tp.cid, tp.check_time
),
max_concurrent AS (
    -- 找出每个视频的最大并发数
    SELECT 
        cid,
        MAX(concurrent_count) as max_count
    FROM concurrent_at_each_time
    GROUP BY cid
)
SELECT 
    cid,
    FORMAT(max_count, 3) as max_concurrent_count
FROM max_concurrent
ORDER BY max_count DESC, cid
LIMIT 3;