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

-- 统计每个科目最大同时在线人数（按course_id排序）

-- 1 先用联合查询，将in_datetime标记为1，out_datetime标记为-1。将结果作为t1表
# select user_id, course_id, in_datetime dt, 1 as flag
# from attend_tb
# union all
# select user_id, course_id, out_datetime dt, -1 as flag
# from attend_tb

-- 2 利用窗口函数，求累计在线人数。结果作为t2表
# select user_id, course_id, dt,
#     sum(flag) over(partition by course_id order by dt rows between unbounded preceding and current row) cum_num
# from t1

-- 3 选择最大在线人数
# select
#     t2.course_id, course_name, max(cum_num)
# from t2 left join course_tb ct on t2.course_id=ct.course_id
# group by course_id, course_name

-- 4 汇总
select
    t2.course_id, course_name, max(cum_num)
from (
    select user_id, course_id, dt,
        sum(flag) over(partition by course_id order by dt rows between unbounded preceding and current row) cum_num
    from (
        select user_id, course_id, in_datetime dt, 1 as flag
        from attend_tb
        union all
        select user_id, course_id, out_datetime dt, -1 as flag
        from attend_tb
    )t1
)t2 left join course_tb ct on t2.course_id=ct.course_id
group by course_id, course_name