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

select t2.course_id,course_name,max(t2.num)
from 
(
select
    t1.course_id,
    sum(flag) over (
        partition by
            t1.course_id
        order by
            dt asc,
            flag desc
    ) as num
from
    (
        select
            course_id,
            in_datetime as dt,
            1 as flag
        from
            attend_tb
        union all
        select
            course_id,
            out_datetime as dt,
            -1 as flag
        from
            attend_tb
    ) as t1
) as t2
left join course_tb
using(course_id)
group by t2.course_id,course_name;