Coding questions · Amazon
List Top Customers and Monthly Order Counts in SQL
This question evaluates a candidate's ability to perform SQL aggregation, grouping, top‑N selection, and date-based summarization to compute customer spend and chronological monthly order counts.
Orders
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 1 | 101 | 2023-01-05 | 120.50 |
| 2 | 102 | 2023-01-10 | 75.00 |
| 3 | 101 | 2023-01-12 | 40.25 |
| 4 | 103 | 2023-01-15 | 220.90 |
| 5 | 104 | 2023-01-20 | 15.00 |
Scenario
Analyst must query customer orders stored in a relational database.
Question
Write SQL to list the top three customers by total spend. Write SQL to return monthly order counts for 2023, ordered chronologically.
Hints
Group-by, aggregates, date functions, ordering.
Top 3 customers by total spend
Using the Orders table, return the top three customers by total spend across all orders. For each customer, compute their total spend, then sort the results by total spend descending. Break ties by customer_id ascending.
Table Orders
| column | type | constraints |
|---|---|---|
| order_id | INTEGER | PRIMARY KEY, NOT NULL |
| customer_id | INTEGER | NOT NULL |
| order_date | DATE | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | customer_id | order_date | order_id |
|---|---|---|---|
| 120.5 | 101 | 2023-01-05 | 1 |
| 75 | 102 | 2023-01-10 | 2 |
| 40.25 | 101 | 2023-01-12 | 3 |
| 220.9 | 103 | 2023-01-15 | 4 |
| 15 | 104 | 2023-01-20 | 5 |
Expected output
Top three customers by total spend across all orders in the sample data, sorted by total spend descending and then customer_id ascending.
| customer_id | total_spend |
|---|---|
| 103 | 220.9 |
| 101 | 160.75 |
| 102 | 75 |
Hints
- Use SUM(amount) grouped by customer_id to calculate total spend per customer.
- Order by total_spend descending and customer_id ascending, then LIMIT 3.
Monthly order counts for 2023
Using the Orders table, return monthly order counts for the calendar year 2023. Output one row per month that has at least one order, labeled as YYYY-MM, ordered chronologically by month.
Table Orders
| column | type | constraints |
|---|---|---|
| order_id | INTEGER | PRIMARY KEY, NOT NULL |
| customer_id | INTEGER | NOT NULL |
| order_date | DATE | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | customer_id | order_date | order_id |
|---|---|---|---|
| 120.5 | 101 | 2023-01-05 | 1 |
| 75 | 102 | 2023-01-10 | 2 |
| 40.25 | 101 | 2023-01-12 | 3 |
| 220.9 | 103 | 2023-01-15 | 4 |
| 15 | 104 | 2023-01-20 | 5 |
Expected output
For the sample data, all orders occur in January 2023, so the result has a single row with the count of 5 for month 2023-01.
| month | order_count |
|---|---|
| 2023-01 | 5 |
Hints
- Filter the date range to 2023 using order_date >= DATE '2023-01-01' and < DATE '2024-01-01'.
- Use DATE_TRUNC to group by month and TO_CHAR to format the month as YYYY-MM.