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

时间戳排序，添加标签然后累计求和即可，常见考题

select course_id,course_name,max(num)
from 
(select course_tb.course_id ,
       course_name,sum(sign) over(partition by course_tb.course_id
                                 order by time,sign desc) as num
                                 FROM
(SELECT user_id,course_id,in_datetime as time,1 as sign
from attend_tb
union 
SELECT user_id,course_id,out_datetime as time,-1 as sign
from attend_tb) base RIGHT join course_tb using(course_id))tmp
group by course_id,course_name
order by course_id