题解 | SQL 189. 牛客直播各科目同时在线人数

WITH t1 AS (
    SELECT course_id,
           user_id,
           in_datetime,
           1 AS uv
    FROM attend_tb
    UNION ALL
    SELECT course_id,
           user_id,
           out_datetime,
           -1 AS uv
    FROM attend_tb
),
t2 AS (
    SELECT course_id,
           in_datetime,
           SUM(uv)
            OVER (PARTITION BY course_id
            ORDER BY in_datetime ASC,
            uv DESC) AS time_num
    FROM t1
),
t3 AS (
    SELECT course_id,
           max(time_num) AS max_num
           FROM t2
    GROUP BY course_id
),
t4 AS (
    SELECT a.course_id,
           a.course_name,
           max_num
    FROM course_tb a
             LEFT JOIN t3
                       ON a.course_id = t3.course_id
    ORDER BY a.course_id ASC
)
SELECT * FROM t4;

# 这里的细节是：按照课程编号分组，时间升序，uv降序，因为先进入直播间再出去直播间！