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

分别对进入时间和离开时间编码为1和-1，以课程作为分割条件对时间排序求和，然后选出其中的最大值即可。

SELECT course_id, course_name, MAX(cnt) AS max_num
FROM
(
	SELECT course_id, course_name, det, SUM(det) OVER(PARTITION BY course_id ORDER BY dt) AS cnt
	FROM
	(
		SELECT course_id, in_datetime AS dt, 1 AS det
		FROM attend_tb
		UNION ALL
		SELECT course_id, out_datetime AS dt, -1 AS det
		FROM attend_tb
	) AS t1
	JOIN course_tb c USING (course_id)
) AS t2
GROUP BY course_id, course_name
ORDER BY course_id