Find top category per region in Aug 2025
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Take-home Project
You are given the following schema and sample data.
Schema:
- customers(customer_id INT, name TEXT, region TEXT)
- orders(order_id INT, customer_id INT, order_date DATE, status TEXT)
- order_items(order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2))
- products(product_id INT, category TEXT)
Sample tables:
customers
+-------------+-------+--------+
| customer_id | name | region |
+-------------+-------+--------+
| 1 | Alice | East |
| 2 | Bob | East |
| 3 | Cara | West |
| 4 | Dan | West |
+-------------+-------+--------+
products
+------------+----------+
| product_id | category |
+------------+----------+
| 10 | Gadgets |
| 11 | Gadgets |
| 12 | Widgets |
+------------+----------+
orders
+----------+-------------+------------+----------+
| order_id | customer_id | order_date | status |
+----------+-------------+------------+----------+
| 101 | 1 | 2025-08-05 | Delivered|
| 102 | 1 | 2025-08-20 | Cancelled|
| 103 | 2 | 2025-08-21 | Delivered|
| 104 | 3 | 2025-08-25 | Delivered|
| 105 | 4 | 2025-08-30 | Delivered|
| 106 | 3 | 2025-09-02 | Delivered|
+----------+-------------+------------+----------+
order_items
+----------+------------+-----+------------+
| order_id | product_id | qty | unit_price |
+----------+------------+-----+------------+
| 101 | 10 | 2 | 50.00 |
| 101 | 12 | 1 | 80.00 |
| 103 | 10 | 1 | 60.00 |
| 103 | 12 | 3 | 30.00 |
| 104 | 11 | 2 | 40.00 |
| 104 | 12 | 1 | 100.00 |
| 105 | 12 | 4 | 25.00 |
| 106 | 10 | 5 | 10.00 |
+----------+------------+-----+------------+
Task: Write a single SQL statement that returns, for each region, the product category with the highest total delivered revenue in August 2025 (2025-08-01 to 2025-08-31). Revenue is SUM(qty*unit_price) from order_items, but only include orders with status='Delivered' and order_date in August 2025. Output columns: region, category, total_revenue. Break ties by choosing the lexicographically smallest category. You must use exactly one subquery or one CTE (no more), and you may not use window functions.
Quick Answer: This question evaluates proficiency in SQL data manipulation skills such as joining related tables, aggregating revenue, filtering by date and status, and implementing deterministic tie-breaking for categories.