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

with t1 as(
    select course_id,course_name,
    in_datetime as mytime,
    1 as uv
    from attend_tb join course_tb using(course_id)
    union all #不能省略all
    select course_id,course_name,
    out_datetime as mytime,
    -1 as uv
    from attend_tb join course_tb using(course_id)
),
t2 as(
    select course_id,course_name,mytime,
    sum(uv) over(partition by course_id order by mytime asc) as online_num
    from t1
)
select course_id,course_name,
max(online_num) as max_num
from t2
group by course_id,course_name
order by 1