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

-- 变成按时间排序的长表
with time_line as 
(
select t1.course_id, course_name, dt, cnt  from course_tb 
right join 
(select user_id, course_id,	in_datetime as dt, 1 as cnt
from attend_tb
UNION ALL
select 
user_id, course_id, out_datetime as dt, -1 as cnt
from 
attend_tb) t1 
on course_tb.course_id = t1.course_id
),

-- 按时间增序累加
cumsum_tb as (
select 
course_id, course_name, dt, cnt,
sum(cnt) over (PARTITION BY course_name ORDER by dt) as cum_sum
from time_line 
order by dt)

select course_id, course_name, max(cum_sum) as max_num from cumsum_tb 
group by course_id, course_name