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

和前面的题思路一致

select b.course_id,course_name,max(同时在线人数)
	   from
(select course_id,
	   sum(时间标识) over(partition by course_id order by 时间,时间标识 desc) as 同时在线人数
       from
	   (select course_id,
       user_id,
       in_datetime as 时间,
       1 as 时间标识
       from attend_tb
       UNION
       (select course_id,
       user_id,
       out_datetime as 时间,
       -1 as 时间标识
       from attend_tb)) a) b
       join course_tb on b.course_id = course_tb.course_id
       group by course_id,course_name
       order by course_id;