select
        a.room_id,
        rit.room_name,
        a.user_count
from (
    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
)a 
left join 
        room_info_tb as rit
on 
        a.room_id = rit.room_id
order by 
        a.user_count desc