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.
Overview: 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.
Read the full Instacart Data Scientist interview experience this question came from
Compute Weekly Revenue by ISO Week
Using the orders table below, compute total weekly revenue by ISO week (Monday–Sunday) across all available data. A week should be identified by its starting date week_start_date = date_trunc('week', order_ts)::date, which corresponds to the Monday of that week. Only include orders with status = 'completed'. Revenue per order is defined as:
revenue_usd = subtotal_usd + tax_usd + delivery_fee_usd - discount_usd
Return one row per week with:
- week_start_date (DATE)
- week_end_date (DATE, Sunday of that week)
- revenue_usd (DECIMAL) as the total revenue for that week
Order the result by week_start_date ascending.
Tables
orders(order_id INT, user_id INT, order_ts TIMESTAMP, geo VARCHAR(10), status VARCHAR(20), subtotal_usd DECIMAL(10,2), tax_usd DECIMAL(10,2), delivery_fee_usd DECIMAL(10,2), discount_usd DECIMAL(10,2))
Hints
- Use date_trunc('week', order_ts) to get the Monday of each ISO week.
- Filter to status = 'completed' before aggregating revenue.
Percent Change Between Last Two Complete Weeks
Using the same orders table, compute the percent change in total revenue between the last two complete ISO weeks in the data, relative to max(order_ts).
Definitions:
- A week is an ISO week: week_start_date = date_trunc('week', order_ts)::date (Monday–Sunday).
- Only orders with status = 'completed' contribute to revenue.
- Revenue per order: revenue_usd = subtotal_usd + tax_usd + delivery_fee_usd - discount_usd.
- Let current_week_start = date_trunc('week', max(order_ts)) based on all orders.
- A "complete" week is any week whose week_start_date is strictly earlier than current_week_start.
Among these complete weeks, find the latest week and the previous week. Return a single row with:
- previous_week_start_date (DATE)
- previous_week_revenue_usd (DECIMAL)
- latest_week_start_date (DATE)
- latest_week_revenue_usd (DECIMAL)
- pct_change_vs_previous (DECIMAL), computed as (latest - previous) / previous * 100, rounded to 2 decimal places.
Use the sample data to illustrate the output.
Tables
orders(order_id INT, user_id INT, order_ts TIMESTAMP, geo VARCHAR(10), status VARCHAR(20), subtotal_usd DECIMAL(10,2), tax_usd DECIMAL(10,2), delivery_fee_usd DECIMAL(10,2), discount_usd DECIMAL(10,2))
Hints
- First reuse a weekly revenue aggregation (a CTE from part A).
- Use date_trunc('week', max(order_ts)) to find the current week, then a window function (ROW_NUMBER) to pick the last two complete weeks.
Attribute a ≥4% Weekly Revenue Drop to Top Geos
Using the orders table and the weekly logic from the previous parts, attribute a significant weekly revenue drop to the top contributing geos.
Steps and definitions:
- As before, define weeks by week_start_date = date_trunc('week', order_ts)::date (Monday–Sunday) and revenue_usd per order as subtotal_usd + tax_usd + delivery_fee_usd - discount_usd.
- Only include orders with status = 'completed' in revenue calculations.
- Let current_week_start = date_trunc('week', max(order_ts)) based on all orders.
- Consider only complete weeks where week_start_date < current_week_start.
- Among these complete weeks, find the latest week and the previous week, and compute overall pct_change_vs_previous = (latest - previous) / previous * 100.
Task:
- If pct_change_vs_previous for the latest complete week is a drop of at least 4% (i.e., pct_change_vs_previous <= -4.0), return the top 3 geos that contribute most to the absolute revenue delta between these two weeks.
- For each such geo, compute:
- previous_week_revenue_usd (DECIMAL)
- latest_week_revenue_usd (DECIMAL)
- revenue_delta_usd = latest_week_revenue_usd - previous_week_revenue_usd (DECIMAL)
- share_of_total_delta_pct (DECIMAL), defined as abs(revenue_delta_usd) / SUM_over_all_geos(abs(revenue_delta_usd)) * 100, rounded to 2 decimal places
- pct_change_vs_previous_geo (DECIMAL), the geo-level percent change (latest - previous) / previous * 100, rounded to 2 decimal places (NULL if previous_week_revenue_usd = 0)
Return at most 3 rows ordered by the absolute revenue change per geo (largest first). If the latest complete week does not show a drop of at least 4%, return an empty result set.
Use the sample data to illustrate the case where there is a 10% drop overall and 3 geos contributing to it.
Tables
orders(order_id INT, user_id INT, order_ts TIMESTAMP, geo VARCHAR(10), status VARCHAR(20), subtotal_usd DECIMAL(10,2), tax_usd DECIMAL(10,2), delivery_fee_usd DECIMAL(10,2), discount_usd DECIMAL(10,2))
Hints
- Build on the weekly aggregation and latest/previous week logic from part B.
- Aggregate revenue by geo and week, then compute geo-level deltas and use ABS() to rank geos by contribution to the total revenue change.