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

with
    t1 as (
        select
            user_id,
            course_id,
            in_datetime as dt,
            1 as diff
        from
            attend_tb
        union all
        select
            user_id,
            course_id,
            out_datetime as dt,
            -1 as diff
        from
            attend_tb
    ),
    t2 as (
        select
            user_id,
            course_id,
            sum(diff) over (
                partition by
                    course_id
                order by
                    dt,
                    diff desc
            ) as num
        from
            t1
    )
select
    t3.course_id,t3.course_name,
    max(num) as max_num
from
    t2 left join course_tb t3 on t2.course_id=t3.course_id
group by
    t3.course_id,t3.course_name;