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

WITH daily_events AS (
    SELECT 
        cid,
        DATE(event_time) AS dt,
        event_time,
        event_type
    FROM (
        -- 生成开始事件
        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
    ) events
),
daily_concurrent AS (
    SELECT 
        cid,
        dt,
        event_time,
        SUM(event_type) OVER (
            PARTITION BY cid, dt 
            ORDER BY event_time, event_type DESC
        ) AS concurrent
    FROM daily_events
),
daily_peak AS (
    SELECT 
        cid,
        dt,
        MAX(concurrent) AS peak_concurrent
    FROM daily_concurrent
    GROUP BY cid, dt
),
video_peak AS (
    SELECT 
        cid,
        MAX(peak_concurrent) AS max_peak_concurrent
    FROM daily_peak
    GROUP BY cid
),
ranked_videos AS (
    SELECT 
        cid,
        max_peak_concurrent,
        DENSE_RANK() OVER (ORDER BY max_peak_concurrent DESC) AS rk
    FROM video_peak
)
SELECT 
    cid AS `视频ID`,
    CAST(max_peak_concurrent AS DECIMAL(10, 3)) AS `最大并发播放量`
FROM ranked_videos
WHERE rk <= 3
ORDER BY max_peak_concurrent DESC, cid
limit 3;

我只能说出题人语文水平烂，说话不严谨，真的很影响刷题的心情