Coding questions · Capital One
Write SQL to find top net-revenue products
This question evaluates proficiency in SQL data manipulation, including joins, aggregations, date-window filtering, handling returns and net-revenue calculations, plus the use of analytic/window functions for per-category ranking and tie-breaking.
Using the sample schema and data below, write a single SQL query that returns, for the last 7 days relative to today (use today = 2025-09-01, so the window is 2025-08-26 to 2025-09-01 inclusive), the top product by net revenue within each category. Output columns: category, product_id, net_units, net_revenue_usd. Definitions: • Consider only orders whose order_date is in the 7-day window. • Net units = sum(qty from order_items for those orders) − sum(qty_returned from returns with return_date also in the 7-day window and matching the same order_id and product_id). • Net revenue = net units × products.price_usd. • If there's a tie on net revenue, break ties by higher net units, then by lexicographically smaller product_id. Use analytic functions (e.g., row_number) to pick winners per category.
Sample tables (ASCII): users(user_id, city) 1, SF 2, NY 3, SF 4, LA
products(product_id, name, category, price_usd)
P1, Desk, Furniture, 200
P2, Chair, Furniture, 120
P3, Monitor, Electronics, 300
P4, Keyboard, Electronics, 80
orders(order_id, user_id, order_date)
O1, 1, 2025-08-26
O2, 2, 2025-08-27
O3, 1, 2025-08-30
O4, 3, 2025-08-31
O5, 4, 2025-09-01
order_items(order_id, product_id, qty)
O1, P1, 1
O1, P2, 2
O2, P3, 1
O3, P3, 1
O3, P4, 3
O4, P2, 1
O5, P1, 1
returns(return_id, order_id, product_id, qty_returned, return_date)
R1, O1, P2, 1, 2025-08-31
R2, O3, P4, 1, 2025-09-01
Top net-revenue product per category in a 7-day window
Table products
| column | type | constraints |
|---|---|---|
| product_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| name | VARCHAR(100) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| price_usd | DECIMAL(10,2) | NOT NULL |
Sample data:
| category | name | price_usd | product_id |
|---|---|---|---|
| Furniture | Desk | 200 | P1 |
| Furniture | Chair | 120 | P2 |
| Electronics | Monitor | 300 | P3 |
| Electronics | Keyboard | 80 | P4 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| order_date | DATE | NOT NULL |
Sample data:
| order_date | order_id | user_id |
|---|---|---|
| 2025-08-26 | O1 | 1 |
| 2025-08-27 | O2 | 2 |
| 2025-08-30 | O3 | 1 |
| 2025-08-31 | O4 | 3 |
| 2025-09-01 | O5 | 4 |
Table order_items
| column | type | constraints |
|---|---|---|
| order_id | VARCHAR(10) | NOT NULL |
| product_id | VARCHAR(10) | NOT NULL |
| qty | INT | NOT NULL |
Sample data:
| order_id | product_id | qty |
|---|---|---|
| O1 | P1 | 1 |
| O1 | P2 | 2 |
| O2 | P3 | 1 |
| O3 | P3 | 1 |
| O3 | P4 | 3 |
| O4 | P2 | 1 |
| O5 | P1 | 1 |
Table returns
| column | type | constraints |
|---|---|---|
| return_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| order_id | VARCHAR(10) | NOT NULL |
| product_id | VARCHAR(10) | NOT NULL |
| qty_returned | INT | NOT NULL |
| return_date | DATE | NOT NULL |
Sample data:
| order_id | product_id | qty_returned | return_date | return_id |
|---|---|---|---|---|
| O1 | P2 | 1 | 2025-08-31 | R1 |
| O3 | P4 | 1 | 2025-09-01 | R2 |
Expected output
One row per category for 2025-08-26 to 2025-09-01 (inclusive), containing the single product with highest net revenue in that category, with ties broken by higher net units then smaller product_id.
| category | net_revenue_usd | net_units | product_id |
|---|---|---|---|
| Electronics | 600 | 2 | P3 |
| Furniture | 400 | 2 | P1 |
Hints
- Filter orders by order_date first, then aggregate order_items by (order_id, product_id).
- Aggregate returns in the same date window by (order_id, product_id) and LEFT JOIN to subtract returned units.
- Use ROW_NUMBER() partitioned by category with the specified tie-break ordering.