Top 3 Customers by Region
SQL coding challenge · Difficulty: medium · Topic: Window Functions · +120 XP
Tables: customers, sales
customers
+---------------+---------+
| Column | Type |
+---------------+---------+
| customer_id | INT | | customer_name | VARCHAR | | region | VARCHAR |
+---------------+---------+
customer_id is the primary key.
sales
+-------------+------+
| Column | Type |
+-------------+------+
| sale_id | INT | | customer_id | INT | | amount | INT |
+-------------+------+
sale_id is the primary key. customer_id references customers.customer_id.
One customer may have many sales rows.
Problem
-------
For each region, find the three customers who spent the most in total.
A customer's total is the sum of amount across all of their sales rows.
If a region has fewer than three customers, return all of them.
Return: region, customer_name, total_amount
Order: region ASC, then total_amount DESC
Requirements
------------
1. Column names must be exactly region, customer_name and total_amount.
2. Return AT MOST three rows per region. A query that returns every customer
with a rank column beside it is not the answer - the extra rows must be
filtered out.
3. No two customers inside the same region have the same total, so ROW_NUMBER(),
RANK() and DENSE_RANK() all give the same result here. Any of them is fine.
Example Input
-------------
customers
+-------------+---------------+--------+
| customer_id | customer_name | region |
+-------------+---------------+--------+
| 1 | Alice | North | | 2 | Bob | North | | 3 | Carol | North | | 4 | Dan | North | | 5 | Eve | South | | 6 | Frank | South | | 7 | Grace | South | | 8 | Henry | West | | 9 | Irene | West |
+-------------+---------------+--------+
sales
+---------+-------------+--------+
| sale_id | customer_id | amount |
+---------+-------------+--------+
| 1 | 1 | 500 | | 2 | 1 | 300 | | 3 | 2 | 400 | | 4 | 2 | 250 | | 5 | 3 | 900 | | 6 | 4 | 200 | | 7 | 4 | 100 | | 8 | 5 | 700 | | 9 | 5 | 150 | | 10 | 6 | 600 | | 11 | 7 | 450 | | 12 | 7 | 50 | | 13 | 8 | 1000 | | 14 | 9 | 250 |
+---------+-------------+--------+
Expected Output
---------------
+--------+---------------+--------------+
| region | customer_name | total_amount |
+--------+---------------+--------------+
| North | Carol | 900 | | North | Alice | 800 | | North | Bob | 650 | | South | Eve | 850 | | South | Frank | 600 | | South | Grace | 500 | | West | Henry | 1000 | | West | Irene | 250 |
+--------+---------------+--------------+
Explanation
-----------
Totals per customer, by region:
North has FOUR customers
Carol 900
Alice 500 + 300 = 800
Bob 400 + 250 = 650
Dan 200 + 100 = 300
The top three are Carol, Alice and Bob. Dan is 4th and is dropped.
South has exactly THREE customers
Eve 700 + 150 = 850
Frank 600
Grace 450 + 50 = 500
All three qualify.
West has only TWO customers
Henry 1000
Irene 250
Both are returned. There is no third row to invent.
That is 3 + 3 + 2 = 8 rows in the result.
What this SQL challenge teaches you
“Top 3 Customers by Region” is a medium-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with JOIN, GROUP BY, ROW_NUMBER, PARTITION BY, Top N — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.
Concepts covered
- JOIN
- GROUP BY
- ROW_NUMBER
- PARTITION BY
- Top N
How to approach it
If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:
- Aggregate first, rank second: you cannot rank a SUM() that has not been computed yet.
- A window function is not allowed in WHERE. Wrap the ranked query in a subquery or CTE, then filter rn <= 3 in the outer query.
How to practise it on PySpark.in
Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.
Related SQL challenges
- Top 3 Products per Category
- Running Total Revenue
- Median Salary per Department
- Latest Order Per Customer
- 3-Day Rolling Sum of Sales
- 7-Day Rolling Purchase Amount by Customer
Frequently asked questions
Do I need to install Spark or a database to solve this?
No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.
Is this challenge free?
Yes - the problem, the sample dataset, the hints and unlimited test runs are free.
What level is it?
It is rated medium and covers Window Functions.