with a as(
    select room_id,
    count(distinct user_id) as user_count
    from user_view_tb
    where (in_time between '23:00:00' and '24:00:00') or (out_time between '23:00:00' and '24:00:00') 
    group by room_id
)
select room_id,room_name,user_count
from a join room_info_tb using(room_id)
order by user_count desc,room_id;