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

with a as(
    select
        course_id,
        in_datetime as time,
        1 as diff
    from 
        attend_tb
    union all
    select 
        course_id,
        out_datetime as time,
        -1 as diff
    from 
        attend_tb
),
b as (
    select 
        course_id,
        sum(diff)over(partition by course_id order by time,diff desc) as nums
    from 
        a
),
c as (
    select
        ct.course_id,
        ct.course_name,
        max(b.nums) as max_num
    from
        course_tb as ct
        left join b on b.course_id = ct.course_id
    group by 
        ct.course_id,
        ct.course_name
)
select * from c