with
t1 as(
    select
        room_id,
        room_name,
        count(distinct user_id) as user_count
    from
        user_view_tb left join room_info_tb using(room_id)
    where
        out_time>='23:00:00'
    group by
        room_id,
        room_name
    order by
        user_count desc,
        room_id
)

select * from t1