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

-- 每个科目最大在线人数，意味着先统计进入，再统计退出，进入为1，退出为-1 
select 
b.course_id 
,course_name 
,max(sum_cnt) as max_num 
from (
select 
course_id 
,dt 
,sum(cnt) over(partition by course_id order by dt) as sum_cnt 
from (
select 
user_id 
,course_id 
,in_datetime  as dt 
,1 as cnt 
from attend_tb 
union 
(select 
user_id 
,course_id 
,out_datetime  as dt 
,-1 as cnt 
from attend_tb ) ) a ) b 
left join course_tb c 
on b.course_id = c.course_id 
group by b.course_id ,course_name 
order by b.course_id