题解 | 牛客直播开始时各直播间在线人数

with t1 as(
    select user_id,course_id,
    in_datetime as mytime,
    "enter" as action
    from attend_tb
    union all
    select user_id,course_id,
    out_datetime as mytime,
    "leave" as action
    from attend_tb
),
t2 as(
    select * from t1 left join course_tb using(course_id)
    where time(mytime) <= "19:00:00"
    order by mytime
)
select distinct course_id,course_name,
sum(if(action="enter",1,-1)) over(partition by course_id) as online_num
from t2
# group by course_id