List Top Customers and Monthly Order Counts in SQL
AmazonData Scientist · Technical Screen
Last updated
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.
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.