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

SELECT t3.course_id
      ,course_name
      ,max_num
FROM
(SELECT course_id
      ,MAX(cnt) AS max_num
FROM  
(
SELECT course_id
      ,SUM(live)OVER(PARTITION BY course_id ORDER BY dt) cnt
FROM
(
SELECT course_id
      ,in_datetime AS dt
      ,1 AS live
FROM attend_tb
UNION ALL
SELECT course_id
      ,out_datetime AS dt
      ,-1 AS live
FROM attend_tb
ORDER BY course_id
) AS t1
) AS t2
GROUP BY course_id
) AS t3 LEFT JOIN course_tb AS t4
ON t3.course_id = t4.course_id
ORDER BY t3.course_id