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

WITH t1 AS 
(
SELECT
course_id,
course_name,
in_datetime AS dt,
1 AS tag

FROM attend_tb
LEFT JOIN course_tb
    USING(course_id)
    
WHERE LEFT(TIME(in_datetime), 5) <= '19:00'
    
UNION ALL

SELECT
course_id,
course_name,
out_datetime AS dt,
-1 AS tag

FROM attend_tb
LEFT JOIN course_tb
    USING(course_id)
    
WHERE LEFT(TIME(out_datetime), 5) <= '19:00'
)

SELECT DISTINCT
course_id,
course_name,
SUM(tag) OVER(PARTITION BY course_id) AS online_num

FROM t1

ORDER BY course_id