query to list matrix users by the amount of rooms they are in

users_rooms.sql

select private_rooms.user_id, public_room_count + private_room_count as total_rooms 
from (select user_id, count(distinct room_id) as private_room_count from users_who_share_private_rooms where user_id <> '' group by user_id) as private_rooms
inner join (select user_id, count(distinct room_id) as public_room_count from users_in_public_rooms where user_id <> '' group by user_id) as public_rooms on
    public_rooms.user_id=private_rooms.user_id
order by total_rooms desc;