Top-Spending Customer per Country
Return country, customer_name, and total_spend for exactly one top-spending customer per country, ordered by total spend descending.
- CTEs
- Window functions
- Joins
- Subqueries
- Aggregation
Challenge brief
Understand the request
International Growth Team is building country-level acquisition targets and wants to identify the biggest spender in each market as a potential brand ambassador.
Find the top-spending customer in each country using two CTEs and ROW_NUMBER() partitioned by country.
Return
- country
- customer_name (full name)
- total_spend (sum of all orders, rounded to 2 decimals)
Constraints
- Return exactly one customer per country
- Break equal spend totals by the lower customer ID
- Exclude customers with no orders
- Order by total spend descending, then country
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
customers
customer_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)countryVARCHAR(50)
orders
order_idINTEGERcustomer_idINTEGERtotal_amountREAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Two-step pattern: (1) aggregate total spend per customer, (2) rank customers within each country and keep rank 1. This is the top-N-per-group pattern with a geographic partition.
Hint 2
Aggregate customer spend first. In the ranking CTE, partition by country and order by spend descending, then customer ID ascending for deterministic ties.
Hint 3
Scaffold: aggregate customer_spend, rank within country by spend and customer ID, then keep position one and apply the published final ordering.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH customer_spend AS (SELECT c.customer_id, c.first_name || ' ' || c.last_name AS customer_name, c.country, ROUND(SUM(o.total_amount), 2) AS total_spend FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.first_name, c.last_name, c.country), ranked AS (SELECT customer_id, customer_name, country, total_spend, ROW_NUMBER() OVER (PARTITION BY country ORDER BY total_spend DESC, customer_id ASC) AS rn FROM customer_spend) SELECT country, customer_name, total_spend FROM ranked WHERE rn = 1 ORDER BY total_spend DESC, country ASC;Why this works
ROW_NUMBER returns exactly one winner per country. Customer ID is the published tie-break contract when two customers have equal spend, and country is the final output tie-break. Customers without any orders are excluded from the spend population.
Success check
3 rows — USA: Bob Smith (74.95), Canada: Carol White (04.98), UK: Emma Davis (9.99)
Expected result
Use this output to verify values, aliases, ordering, and row count.
| country | customer_name | total_spend |
|---|---|---|
| USA | Bob Smith | 374.95 |
| Canada | Carol White | 104.98 |
| UK | Emma Davis | 39.99 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Uber
Independent Uber-style mobility marketplace SQL practice covering trips, drivers, riders, pricing, payments, and promotions.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Return to the complete interview preparation experience.