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

#同时在线最大人数问题，主要思路为上线设置为1，下线设置为-1，通过开窗函数加总可以得到同时在线人数
with a as (select sum(status)over(partition by a.course_id order by time) as max_num,a.course_id,course_name
from
(select course_id,in_datetime as time,case when in_datetime is not null then 1 end as status
from attend_tb 
union all
select course_id,out_datetime as time,case when out_datetime is not null then -1 end as status 
from attend_tb) a
left join course_tb b
on a.course_id=b.course_id)

select course_id,course_name,max(max_num)
from a 
group by course_id,course_name