List Top Customers and Monthly Order Counts in SQL
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
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.
Tables
Orders(order_id INTEGER, customer_id INTEGER, order_date DATE, amount DECIMAL(10,2))
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.
Tables
Orders(order_id INTEGER, customer_id INTEGER, order_date DATE, amount DECIMAL(10,2))
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.