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

with t1 as (
    select user_id,course_id,in_datetime dt ,1 as uv
    from attend_tb
    union
    select user_id,course_id,out_datetime dt,-1 as uv
    from attend_tb
),
t2 as (
    select * ,sum(uv) over(partition by course_id order by dt )  num
    from t1
),
t3 as(
    select course_id,max(num)  max_num
    from t2
    group by course_id
)


select t3.course_id,t4.course_name , max_num
from t3 
left join course_tb t4
on t3.course_id =t4.course_id