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

select
    course_id,
    course_name,
    max(cnt)as max_num
from(select
    course_id,
    course_name,
    sum(flag) over(partition by course_id order by event_time) as cnt
from(select
    at.course_id,
    ct.course_name,
    date_format(ct.course_datetime, '%Y-%m-%d %H:%i:00') as start_time,
    date_format(concat(date_format(ct.course_datetime, '%Y-%m-%d'), ' ', substring_index(substring_index(ct.course_datetime, ' ', -1), '-', -1)), '%Y-%m-%d %H:%i:00') as end_time,
    in_datetime as event_time,
    1 as flag
from attend_tb at
left join course_tb ct
on at.course_id = ct.course_id
union all
select
    at.course_id,
    ct.course_name,
    date_format(ct.course_datetime, '%Y-%m-%d %H:%i:00') as start_time,
    date_format(concat(date_format(ct.course_datetime, '%Y-%m-%d'), ' ', substring_index(substring_index(ct.course_datetime, ' ', -1), '-', -1)), '%Y-%m-%d %H:%i:00') as end_time,
    out_datetime as event_time,
    -1 as flag
from attend_tb at
left join course_tb ct
on at.course_id = ct.course_id)t1
)t2
group by course_id, course_name