The busiest customer
mediumThe busiest customer
Amazon SQL Interview Question
Amazon's marketing team wants to feature its most active customer in a customer story.
Find the name of the customer who has placed the most orders. Count every order, whatever its status. If several customers are tied for the most orders, return all of them.
Asked of
- Data Analyst
- Product Analyst
- Data Scientist
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 |
|---|
| Priya Sharma |
Explanation
Priya Sharma placed three orders in the example, including one that was refunded. Every other customer placed two orders or fewer, so Priya Sharma is the only name returned.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Amazon
Difficulty
medium
Topic
subqueries
Your query
Starting the SQL engine...