题解 | 短视频直播间晚上11-12点之间各直播间的在线人数

WITH t1 AS (
    SELECT
    room_id,
    count(DISTINCT user_id) user_count
    FROM user_view_tb
    WHERE hour(in_time) BETWEEN 23 AND 24 OR hour(out_time) BETWEEN 23 AND 24
    GROUP BY room_id
)
SELECT 
t1.room_id room_id,
room_name,
t1.user_count user_count
FROM room_info_tb JOIN t1 USING(room_id)
ORDER BY user_count DESC