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

with st as
(
    select course_id,in_datetime
    from attend_tb at
    group by course_id,in_datetime
)
select a.course_id,course_name,max(cnt) max_num from
(select *,(select sum(case when
st.in_datetime >=at.in_datetime and st.in_datetime<=at.out_datetime then 1
else 0 end)
from attend_tb at where at.course_id=st.course_id
) as cnt from st)a,course_tb ct
where a.course_id=ct.course_id
group by a.course_id,course_name
order by a.course_id