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

-- 1. 将播放记录的起止时间(start/end)拆解为+1和-1的离散事件流。
WITH events AS (
    SELECT cid, start_time as event_time, 1 as event_type
    FROM play_record_tb
    UNION ALL
    SELECT cid, end_time as event_time, -1 as event_type  
    FROM play_record_tb
),

-- 2. 利用窗口函数计算每个时间点的并发数。
running_totals AS (
    SELECT 
        cid,
        event_time,
        -- 通过累加事件(+1/-1)，计算出当前时间点的并发总数。
        SUM(event_type) OVER (
            PARTITION BY cid 
            -- 关键排序：确保同一时刻的+1事件在-1事件之前处理，以捕捉到正确的峰值。
            ORDER BY event_time, event_type DESC 
            ROWS UNBOUNDED PRECEDING
        ) as concurrent_count
    FROM events
),

-- 3. 聚合得到每个视频的历史最大并发数。
max_concurrent AS (
    SELECT 
        cid,
        MAX(concurrent_count) as max_count
    FROM running_totals
    GROUP BY cid
)

-- 4. 输出Top 3结果。
SELECT 
    cid,
    FORMAT(max_count, 3) as max_concurrent_count
FROM max_concurrent
ORDER BY max_count DESC, cid
LIMIT 3;