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

WITH t1 AS(
SELECT
user_id,
course_id,
in_datetime AS dt,
1 AS tag

FROM attend_tb

UNION ALL

SELECT
user_id,
course_id,
out_datetime AS dt,
-1 AS tag

FROM attend_tb
)

SELECT
course_id,
course_name,
MAX(num) AS max_num

FROM(
    SELECT
    course_id,
    course_name,
    SUM(tag) OVER(PARTITION BY course_id ORDER BY dt) AS num

    FROM t1
    LEFT JOIN course_tb
        USING(course_id)) AS t2

GROUP BY course_id, course_name

ORDER BY course_id