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

select a.course_id,
       b.course_name,
       a.max_num
from (select course_id, 
             max(num) max_num
      from (select course_id , 
                   tms ,
                   sum(tag) over(partition by course_id order by tms) as num
            from (select user_id,
                         course_id,
                         in_datetime tms, 
                         1 as tag
                  from attend_tb
                  union all
                  select user_id,
                         course_id,
                         out_datetime tms,
                         -1 as tag
                  from attend_tb) t )t
      group by course_id ) a
join course_tb b
on a.course_id = b.course_id