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

with
    t1 as(
        #1、标记登录记录
        select 
            user_id,
            course_id,
            in_datetime `dt` ,
            1 `flag`
        from attend_tb `at`
    ),
    t2 as(
         #2、标记登出记录
            select 
            user_id,
            course_id,
            out_datetime  `dt`,
            -1 `flag`
        from attend_tb `at`
    ),
    t3 as(
        #3、将两张表记录汇总（用union即可）
        select *
        from t1
        union 
        select *
        from t2
    ),
    t4 as(
        #4、记录该行记录的登录的时候有几个在线
        select 
            *,
            sum(flag) over(partition by course_id order by dt rows between unbounded preceding and current row) `nums` 
        from t3
    )
# 5、选出每个科目最大同时在线人数
select 
    t4.course_id,
    course_name,
    max(nums) `max_num`
from t4
join course_tb `ct`
on t4.course_id=ct.course_id
group by course_id,course_name
order by course_id


## 其实在group by和order by 就可以不用加表名前缀