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

select 
    course_id
    ,course_name
    ,max(num)
from
(
select 
    t1.course_id     course_id
    ,c.course_name   course_name
    ,t1.user_id      user_id
    ,dt              dt
    ,sum(t1.uv)over(partition by t1.course_id order by dt, t1.user_id) num
from 
(
select 
    user_id
    ,course_id
    ,in_datetime dt
    ,1 uv
from 
    attend_tb
union all 
select 
    user_id
    ,course_id
    ,out_datetime dt
    ,-1 uv
from 
    attend_tb
) t1
left join 
    course_tb c 
on 
    t1.course_id=c.course_id 
) t2 
group by 
    course_id
    ,course_name
order by 
    course_id