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

SELECT tmp2.course_id,course_name,max(cnt) as max_num
FROM
(
    SELECT course_id, user_id, tms, status,
    SUM(status) over(partition BY course_id ORDER BY tms ASC) as cnt
    FROM(
         SELECT course_id, user_id, in_datetime as tms,1 as status
         FROM attend_tb
         UNION ALL
         SELECT course_id, user_id, out_datetime as tms,-1 as status
         FROM attend_tb
    ) as tmp
)as tmp2
join course_tb ON tmp2.course_id=course_tb.course_id
GROUP BY tmp2.course_id,course_name
ORDER BY tmp2.course_id