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

• 进入直播间记人数+1
• 出直播间记人数-1
• 使用sum（人数）over（按课程id分组 按进出时间，人数降序）
• 按课程分组找到前一步的最大值

select
course_id,
course_name,
max(num) as maxnum
from(
select
course_id,
course_name,
sum(u)over(partition by course_id order by t,u desc) as num
from(
select 
course_id,
course_name,
in_datetime as t,
1 as u
from attend_tb
join course_tb using(course_id)

union all 

select 
course_id,
course_name,
out_datetime as t,
-1 as u
from  attend_tb
join course_tb using(course_id)
)t1

)t2
group by course_id,course_name