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