Top spending customers
hardTop spending customers
Amazon SQL Interview Question
Amazon's loyalty team is inviting its three biggest customers to an exclusive preview event.
Find the three customers who have spent the most on completed orders. Show their name and total spend as total_spent. Sort the rows by total_spent, highest first.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
ordersTable35 rows
| Column Name | Type |
|---|---|
| order_id | BIGINT |
| customer_id | BIGINT |
| order_date | DATE |
| status | VARCHAR |
| amount | DOUBLE |
customersTable10 rows
| Column Name | Type |
|---|---|
| customer_id | BIGINT |
| name | VARCHAR |
| country | VARCHAR |
| segment | VARCHAR |
| signup_date | DATE |
ordersExample Input
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2024-01-05 | completed | 120.5 |
| 1002 | 2 | 2024-01-07 | completed | 2400 |
| 1003 | 1 | 2024-01-11 | completed | 89.99 |
| 1004 | 3 | 2024-01-15 | canceled | 45 |
| 1005 | 4 | 2024-01-18 | completed | 610.75 |
| 1006 | 2 | 2024-01-22 | completed | 1850.25 |
| 1007 | 5 | 2024-01-29 | completed | 3200 |
| 1008 | 1 | 2024-02-02 | refunded | 75 |
| 1009 | 6 | 2024-02-06 | completed | 55.4 |
| 1010 | 3 | 2024-02-09 | completed | 132.6 |
customersExample Input
| customer_id | name | country | segment | signup_date |
|---|---|---|---|---|
| 1 | Priya Sharma | India | consumer | 2023-01-14 |
| 2 | Marcus Webb | United States | enterprise | 2023-02-03 |
| 3 | Ana Sousa | Brazil | consumer | 2023-02-27 |
| 4 | Liam O'Connor | Ireland | small_business | 2023-03-15 |
| 5 | Yuki Tanaka | Japan | enterprise | 2023-04-02 |
| 6 | Fatima Al-Rashid | United Arab Emirates | consumer | 2023-05-21 |
| 7 | Daniel Okafor | Nigeria | small_business | 2023-06-08 |
Example Output
| name | total_spent |
|---|---|
| Marcus Webb | 4250.25 |
| Yuki Tanaka | 3200 |
| Liam O'Connor | 610.75 |
Explanation
Marcus Webb spent 4,250.25 across two completed orders, more than anyone else in the example. Yuki Tanaka is second with 3,200 and Liam O'Connor is third with 610.75. Everyone else spent less, so they are not invited.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Amazon
Difficulty
hard
Topic
joins
Your query
Starting the SQL engine...