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.
Quick Answer: 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.