Write SQL to find top net-revenue products
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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
Overview: 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.
Read the full Capital One Data Scientist interview experience this question came from
Using the tables below, write a single SQL query that returns, for the date window 2025-08-26 through 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 within 2025-08-26 to 2025-09-01 (inclusive).
- Net units = (sum of qty from order_items for those orders) − (sum of qty_returned from returns where return_date is also within 2025-08-26 to 2025-09-01 and matches the same order_id and product_id).
- Net revenue = net_units × products.price_usd.
- If there's a tie on net revenue within a category, break ties by higher net units, then by lexicographically smaller product_id.
Requirement: Use an analytic function (e.g., ROW_NUMBER) to pick the winner per category.
Tables
products(product_id VARCHAR(10), name VARCHAR(100), category VARCHAR(50), price_usd DECIMAL(10,2))
orders(order_id VARCHAR(10), user_id INT, order_date DATE)
order_items(order_id VARCHAR(10), product_id VARCHAR(10), qty INT)
returns(return_id VARCHAR(10), order_id VARCHAR(10), product_id VARCHAR(10), qty_returned INT, return_date DATE)
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.