How could I use the select statement on feature from a subquery ? (Postgree)

Question

I'm training for an interview and trying to solve a query, I would like to find for each city who is the client who spent the most. I got the good result the max spent by city but I get an error when I'm trying to retrieve the name and lastname of my customer who spent this amount. Is there an efficient way to do it ? Thank you!

select max(total_payment),X.city, X.firstname, X.lastname
from (
select sum(amount) as total_payment, c.customer_id, cit.city_id, cit.city as city, c.first_name as firstname, c.last_name as lastname
from payment p
inner join customer as c on p.customer_id=c.customer_id
inner join address as ad on c.address_id=ad.address_id
inner join city as cit on ad.city_id=cit.city_id
group by c.customer_id, cit.city_id
order by city
) as X
group by X.city

Target result column: The name and last name of the customer who spent the most for each city.

120,Paris,Nicolas, Dupont

130, Madrid, Raul, Garcia

70, London,Dave, Goldman

Answer 1

You want window functions:

select cc.*
from (select sum(p.amount) as total_payment, c.customer_id, cit.city_id, 
             cit.city as city, c.first_name as firstname, c.last_name as lastname,
             row_number() over (partition by cit.city order by sum(p.amount) desc) as seqnum
      from payment p join
           customer c
           on p.customer_id = c.customer_id join
           address ad
           on c.address_id = ad.address_id join
           city cit
           on ad.city_id = cit.city_id
      group by c.customer_id, cit.city_id
     ) cc
where seqnum = 1;

Note that your query has two errors that should fail any interview:

1. You are using ORDER BY in a subquery. According to the standard and most databases, ORDER BY is either not allowed or ignored.
2. In your outer query, the GROUP BY columns are inconsistent with the unaggregated SELECT columns. Once again, this violates the standard and most databases return a syntax error.