How to return the top 3 spending customers per country?

Question

I'm trying to return the top 3 spending customers per country for a table like this:

customer_id	country	spend
159	China	45
152	China	8
159	China	21
160	China	6
161	China	9
162	China	93
152	China	3
168	Germany	91
169	Germany	101
170	Germany	38
171	Germany	17
154	Germany	11
154	Germany	50
167	Germany	63
168	Germany	1
153	Japan	7
163	Japan	58
164	Japan	44
153	Japan	19
164	Japan	10
165	Japan	15
166	Japan	24
153	Japan	105

I've tried the below code but it's not returning the correct results.

SELECT customer_id, country, spend FROM (SELECT customer_id, country, spend,
            @country_rank := IF(@current_country = country, @country_rank + 1, 1)
             AS country_rank,
            @current_country := country
       FROM table1
       ORDER BY country ASC, spend DESC) ranked_rows
       WHERE country_rank<=3;

Since some customers are also repeat customers, I want to make sure that it's the sum of spend per customer that's being taken into account.

Answer 1

You appear to be using MySQL. If you're running version 8 or later, then just use ROW_NUMBER() here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY country ORDER BY spend DESC) rn
    FROM table1
)

SELECT customer_id, country, spend
FROM cte
WHERE rn <= 3;