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

select b.course_id,c.course_name
        ,max(b.seeing_num) as max_num
from 
(
      select a.course_id
              ,sum(a.count_num) over(partition by a.course_id,date(a.dt) order by a.dt asc,a.count_num desc) as seeing_num
      from
      (
            select user_id
                    ,course_id
                    ,in_datetime as dt
                    ,1 as count_num
            from attend_tb
            union ALL

            select user_id
                    ,course_id
                    ,out_datetime as dt
                    ,-1 as count_num
            from attend_tb) a)b
left join course_tb c
on b.course_id = c.course_id
group by b.course_id,c.course_name
order by b.course_id asc;