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

with status_table as (
    select course_id,user_id,in_datetime as watchtime,1 as status
    from
    attend_tb
    union
    select course_id,user_id,out_datetime as watchtime,-1 as status
    from
    attend_tb
    order by watchtime

)


select 
course_id,course_name,max(num_in_time) as max_num
from
(
select 
course_id,
course_name,
watchtime,
status,
sum(status) over(partition by course_id order by watchtime) as num_in_time
from status_table left join course_tb using(course_id) ) t1
group by course_id,course_name
order by course_id

根据进入时间和出去时间建立一个状态变更表，然后分组按时间顺序进行使用窗口函数进行加和操作，再选取max人数就好了