select
    u.room_id,
    r.room_name,
    count(distinct u.user_id) as user_count
from 
    user_view_tb u left join room_info_tb r
on 
    u.room_id=r.room_id
where
    u.out_time >='23:00:00'
    and
    u.in_time <='24:00:00'
group by 
    u.room_id,r.room_name
order by
    count(distinct u.user_id) desc