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

select 
    course_id,
    course_name,
    max(cnt) as max_num
from (
    select 
        course_id,
        course_name,
        sum(uv) over (partition by course_id order by tm asc,uv desc) as cnt
    from (
        select 
            course_id,
            user_id,
            in_datetime as tm,
            1 as uv
        from 
            attend_tb
        union all 
        select 
            course_id,
            user_id,
            out_datetime as tm,
            -1 as uv
        from 
            attend_tb) as a
    left join course_tb as b
    using(course_id)
            ) as b
group by 1,2
order by course_id asc