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

select course_id,course_name,max(num)
from(
    select course_id,time,situation,
        sum(situation) over(partition by course_id order by time rows between unbounded preceding and current row) num
        from (
            select user_id,course_id,in_datetime  time,1 situation from attend_tb
            union 
            select user_id,course_id,out_datetime time ,-1 situation from attend_tb
        )  UN
    ) num
left join course_tb using(course_id)
group by course_name,course_id