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

with
    c as (
        select
            room_id,
            count(distinct user_id) as user_count
        from
            user_view_tb
        where
            hour (in_time) in ('22', '23')
            or hour (out_time) in ('22', '23')
        group by
            room_id
        order by
            user_count desc
    )
select
    c.room_id,
    b.room_name,
    c.user_count
from
    c
    left join room_info_tb b on c.room_id = b.room_id