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

with t0 as(
select user_id, a.course_id, in_datetime, out_datetime,course_name
from attend_tb a
join course_tb c on a.course_id = c.course_id
),


t1 as(
select user_id,course_id,course_name, in_datetime time, 1 mark from t0
union all
select user_id,course_id,course_name, out_datetime time, -1 mark from t0
),

t2 as(
select *,
       sum(mark) over (partition by course_id order by time
                 rows between unbounded preceding and current row) num
from t1
order by course_id,time, user_id
)

select course_id,course_name,max(num) max_num
from t2
group by course_id,course_name
order by course_id