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

# 1.加标签+连接查询
# 2.联结课程表
# 3.聚合窗口函数SUM()OVER(PARTITION BY ORDER BY)统计各科在线人数
# 4. 查询最大在线人数
SELECT t3.course_id, t3.course_name, MAX(t3.uv) AS max_num
FROM

(SELECT t1.course_id, t2.course_name, 
SUM(t1.tag) OVER(PARTITION BY t1.course_id ORDER BY t1.dt) AS uv
FROM 
(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) AS t1
INNER JOIN course_tb AS t2
ON t1.course_id	= t2.course_id) AS t3

GROUP BY t3.course_id, t3.course_name
ORDER BY t3.course_id;