0

I have to query most valued customer from each stores and i got out put with

most valued customer (customer with the most rentals)
    city    | customer_name | most_valued_customer 
------------+---------------+----------------------
 Leeds      | Liam          |                   10
 Leeds      | Vel           |                    8
 Leeds      | Jim           |                    7
 Leeds      | David         |                    6
 Manchester | Alex          |                    7
 Manchester | Duncan        |                    6
 Manchester | Cat           |                    2


SELECT
DISTINCT stores.city,
customers.customer_name,
COUNT(rental.customer_id) AS most_valued_customer
FROM rental

JOIN customers
ON rental.customer_id = customers.customer_id

JOIN stores
ON customers.location = stores.city

GROUP BY stores.city,
customers.customer_name,
rental.customer_id

ORDER BY city, COUNT(rental.customer_id) DESC;

How can i get most valued customer from each city? output should be

 city    | customer_name | most_valued_customer 
------------+---------------+----------------------
 Leeds      | Liam          |                   10
 Manchester | Alex          |                    7

please help!

I've tried MAX and limit 1 but making error.

hardwork_gummy_bear
  • 11
  • 1
  • Have you tried using [`ROW_NUMBER`](https://www.postgresqltutorial.com/postgresql-window-function/postgresql-row_number/)? – lemon May 25 '23 at 23:38
  • look up either `cross apply` or `lateral` or `row_number` as mentioned before – xQbert May 25 '23 at 23:42

0 Answers0