SELECT u.room_id,room_name,COUNT(DISTINCT user_id) AS user_count
FROM user_view_tb AS u JOIN room_info_tb  AS r on u.room_id = r.room_id
where (time(in_time)>='23:00:00' AND time(in_time)<='24:00:00') OR (time(out_time)>='23:00:00' AND time(out_time)<='24:00:00' )
GROUP BY room_name,u.room_id
ORDER BY user_count DESC,u.room_id