Issue with getting the rank of a user based on combined columns in a join table

I have a users table and each user has flights in a flights table. Each flight has a departure and an arrival airport relationship within an airports table. What I need to do is count up the unique airports across both departure and arrival columns (flights.departure_airport_id and flights.arrival_airport_id) for each user, and then assign them a rank via dense_rank and then retrieve the rank for a given user id.

Basically, I need to order all users according to how many unique airports they have flown to or from and then get the rank for a certain user.

Here’s what I have so far:

SELECT u.rank FROM (
    SELECT 
        users.id,
        dense_rank () OVER (ORDER BY count(DISTINCT (flights.departure_airport_id, flights.arrival_airport_id)) DESC) AS rank
    FROM users 
    LEFT JOIN flights ON users.id = flights.user_id
    GROUP BY users.id
) AS u WHERE u.id = 'uuid';

This works, but does not actually return the desired result as count(DISTINCT (flights.departure_airport_id, flights.arrival_airport_id)) counts the combined airport ids and not each unique airport id separately. That’s how I understand it works, anyway… I’m guessing that I somehow need to use a UNION join on the airport id columns but can’t figure out how to do that.

I’m on Postgres 13.0.

Answer

I would recommend a lateral join to unpivot, then aggregation and ranking:

select *
from (
    select f.user_id, 
        dense_rank() over(order by count(distinct a.airport_id) desc) rn
    from flights f 
    cross join lateral (values 
        (f.departure_airport_id), (f.arrival_airport_id)
    ) a(airport_id)
    group by f.user_id
) t
where user_id = 'uuid'

You don’t really need the users table for what you want, unless you do want to allow users without any flight (they would all have the same, highest rank). If so:

select *
from (
    select u.id, 
        dense_rank() over(order by count(distinct a.airport_id) desc) rn
    from users u
    left join flights f on f.user_id = u.id
    left join lateral (values 
        (f.departure_airport_id), (f.arrival_airport_id)
    ) a(airport_id) on true
    group by u.id
) t
where id = 'uuid'