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

with a as (select x.course_id,course_name,in_datetime as dt,1 as nb from attend_tb x join course_tb y using(course_id) union all select x.course_id,course_name,out_datetime as dt,-1 as nb from attend_tb x join course_tb y using(course_id) order by 3,4), b as (select a.course_id,a.course_name,sum(a.nb) over (partition by a.course_id order by a.dt) as at_time from a) select b.course_id,b.course_name,max(b.at_time) from b group by 1,2 order by 1