Aggregate weekly revenue and attribute 4% drop
Company: Instacart
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Write SQL over the following schema to: (A) compute weekly revenue by ISO week (Monday–Sunday) from orders, excluding cancelled/refunded; revenue_usd = subtotal_usd + tax_usd + delivery_fee_usd − discount_usd. (B) Return the percent change between the last two complete weeks in the data (relative to max(order_ts)). (C) If the latest week shows a ≥4% drop vs the prior week, output the top 3 geos contributing most to the absolute revenue delta, with each geo’s share of the total delta and its own week-over-week percent change. Schema and small sample data:
Table: orders
- order_id INT
- user_id INT
- order_ts TIMESTAMP UTC
- geo VARCHAR(10)
- status VARCHAR(20) -- 'completed','cancelled','refunded'
- subtotal_usd DECIMAL(10,2)
- tax_usd DECIMAL(10,2)
- delivery_fee_usd DECIMAL(10,2)
- discount_usd DECIMAL(10,2)
Sample rows (UTC):
+----------+---------+---------------------+------+-----------+--------------+---------+-------------------+--------------+
| order_id | user_id | order_ts | geo | status | subtotal_usd | tax_usd | delivery_fee_usd | discount_usd |
+----------+---------+---------------------+------+-----------+--------------+---------+-------------------+--------------+
| 1 | 101 | 2025-08-12 14:00:00 | MIA | completed | 50.00 | 3.50 | 5.99 | 0.00 |
| 2 | 102 | 2025-08-13 16:00:00 | MIA | completed | 40.00 | 2.80 | 5.99 | 5.00 |
| 3 | 103 | 2025-08-18 12:00:00 | ATL | completed | 60.00 | 4.20 | 0.00 | 0.00 |
| 4 | 104 | 2025-08-19 10:00:00 | ATL | cancelled | 35.00 | 2.45 | 0.00 | 0.00 |
| 5 | 105 | 2025-08-20 09:00:00 | MIA | completed | 30.00 | 2.10 | 5.99 | 0.00 |
| 6 | 106 | 2025-08-20 18:00:00 | NYC | completed | 80.00 | 5.60 | 0.00 | 10.00 |
| 7 | 107 | 2025-08-25 13:30:00 | NYC | refunded | 20.00 | 1.40 | 0.00 | 0.00 |
| 8 | 108 | 2025-08-26 15:15:00 | MIA | completed | 55.00 | 3.85 | 5.99 | 0.00 |
+----------+---------+---------------------+------+-----------+--------------+---------+-------------------+--------------+
Notes: Treat week_start = date_trunc('week', order_ts) with weeks starting Monday; exclude rows where status <> 'completed'. Return clear, typed columns and guard against partial current week by considering only completed weeks.
Quick Answer: This question evaluates a candidate's ability to perform time-based revenue aggregation, filtering, percent-change calculation, and geo-level attribution using SQL or Python, with attention to ISO-week alignment and revenue computation.