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

select
  N.ci,
  N.cn,
  max(N.max_num)
from
  (
    select
      M.course_id ci,
      ct.course_name cn,
      sum(M.cou) over(
        PARTITION by M.course_id
        order by
          M.t,
          M.cou desc
      ) max_num
    from
      (
        select
          in_datetime t,
          user_id,
          1 cou,
          course_id
        from
          attend_tb
        union all
        select
          out_datetime t,
          user_id,
          -1 cou,
          course_id
        from
          attend_tb
      ) M
      inner join course_tb ct on M.course_id = ct.course_id
  ) N
group by
  N.ci,
  N.cn
order by
  N.ci