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

WITH t1 AS (
    SELECT *
    FROM user_view_tb
    WHERE
        HOUR(out_time) = 23
            OR (HOUR(out_time) = 24
                  AND MINUTE(out_time) = 0
                  AND SECOND(out_time) = 0)
),
t2 AS (
    SELECT
        room_id,
        count(DISTINCT user_id) AS user_count
    FROM t1
    GROUP BY room_id
    ORDER BY user_count DESC
),
t3 AS (
    SELECT t2.room_id,
           b.room_name,
           user_count
    FROM t2
    LEFT JOIN room_info_tb b
    ON t2.room_id = b.room_id
)SELECT * FROM t3;

# 这道题的关键在于按照房间，计算去重的用户数！