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

with t1 as (
    select
        course_id,
        in_datetime as dt,
        1 as alpha
    from 
        attend_tb
    union all 
    select
        course_id,
        out_datetime as dt,
        -1 as alpha
    from 
        attend_tb
)

select
    t3.course_id,
    course_tb.course_name,
    t3.max_num
from (
    select
        course_id,
        max(current_num) as max_num
    from (
        select
            course_id,
            dt,
            sum(alpha) over (partition by course_id order by dt) as current_num
        from 
            t1
    ) as t2
    group by 
        course_id
) as t3
left join 
    course_tb
on 
    t3.course_id = course_tb.course_id
order by
    t3.course_id