Coding questions · Instacart
Aggregate weekly revenue and attribute 4% drop
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.
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.
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.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| order_ts | TIMESTAMP | NOT NULL |
| geo | VARCHAR(10) | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| subtotal_usd | DECIMAL(10,2) | NOT NULL |
| tax_usd | DECIMAL(10,2) | NOT NULL |
| delivery_fee_usd | DECIMAL(10,2) | NOT NULL |
| discount_usd | DECIMAL(10,2) | NOT NULL |
Sample data:
| delivery_fee_usd | discount_usd | geo | order_id | order_ts | status | subtotal_usd | tax_usd | user_id |
|---|---|---|---|---|---|---|---|---|
| 5 | 0 | MIA | 1 | 2025-08-11 10:00:00 | completed | 50 | 5 | 101 |
| 3 | 0 | MIA | 2 | 2025-08-12 14:00:00 | completed | 34 | 3 | 102 |
| 2 | 0 | ATL | 3 | 2025-08-13 16:00:00 | completed | 45 | 3 | 103 |
| 3 | 0 | NYC | 4 | 2025-08-14 09:30:00 | completed | 44 | 3 | 104 |
| 2 | 0 | MIA | 5 | 2025-08-18 12:00:00 | completed | 26 | 2 | 105 |
| 2 | 0 | ATL | 6 | 2025-08-19 18:00:00 | completed | 64 | 4 | 106 |
| 3 | 0 | NYC | 7 | 2025-08-20 11:00:00 | completed | 72 | 5 | 107 |
| 2 | 0 | NYC | 8 | 2025-08-21 17:00:00 | refunded | 40 | 3 | 108 |
| 1 | 0 | MIA | 9 | 2025-08-25 13:30:00 | completed | 17 | 2 | 109 |
| 0 | 0 | ATL | 10 | 2025-08-26 15:15:00 | completed | 9 | 1 | 110 |
| 0 | 0 | ATL | 11 | 2025-08-18 20:00:00 | cancelled | 100 | 5 | 111 |
Expected output
One row per ISO week (Monday–Sunday) with the week start date, week end date, and total completed-order revenue in that week. Using the sample data, all three weeks present in the data are returned.
| revenue_usd | week_end_date | week_start_date |
|---|---|---|
| 200 | 2025-08-17 | 2025-08-11 |
| 180 | 2025-08-24 | 2025-08-18 |
| 30 | 2025-08-31 | 2025-08-25 |
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.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| order_ts | TIMESTAMP | NOT NULL |
| geo | VARCHAR(10) | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| subtotal_usd | DECIMAL(10,2) | NOT NULL |
| tax_usd | DECIMAL(10,2) | NOT NULL |
| delivery_fee_usd | DECIMAL(10,2) | NOT NULL |
| discount_usd | DECIMAL(10,2) | NOT NULL |
Sample data:
| delivery_fee_usd | discount_usd | geo | order_id | order_ts | status | subtotal_usd | tax_usd | user_id |
|---|---|---|---|---|---|---|---|---|
| 5 | 0 | MIA | 1 | 2025-08-11 10:00:00 | completed | 50 | 5 | 101 |
| 3 | 0 | MIA | 2 | 2025-08-12 14:00:00 | completed | 34 | 3 | 102 |
| 2 | 0 | ATL | 3 | 2025-08-13 16:00:00 | completed | 45 | 3 | 103 |
| 3 | 0 | NYC | 4 | 2025-08-14 09:30:00 | completed | 44 | 3 | 104 |
| 2 | 0 | MIA | 5 | 2025-08-18 12:00:00 | completed | 26 | 2 | 105 |
| 2 | 0 | ATL | 6 | 2025-08-19 18:00:00 | completed | 64 | 4 | 106 |
| 3 | 0 | NYC | 7 | 2025-08-20 11:00:00 | completed | 72 | 5 | 107 |
| 2 | 0 | NYC | 8 | 2025-08-21 17:00:00 | refunded | 40 | 3 | 108 |
| 1 | 0 | MIA | 9 | 2025-08-25 13:30:00 | completed | 17 | 2 | 109 |
| 0 | 0 | ATL | 10 | 2025-08-26 15:15:00 | completed | 9 | 1 | 110 |
| 0 | 0 | ATL | 11 | 2025-08-18 20:00:00 | cancelled | 100 | 5 | 111 |
Expected output
A single row showing the previous and latest complete weeks (relative to max(order_ts)) and the percent change in revenue between them. For the sample data, current_week_start is 2025-08-25, so the last two complete weeks are 2025-08-11 and 2025-08-18, with a 10% drop in revenue.
| latest_week_revenue_usd | latest_week_start_date | pct_change_vs_previous | previous_week_revenue_usd | previous_week_start_date |
|---|---|---|---|---|
| 180 | 2025-08-18 | -10 | 200 | 2025-08-11 |
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.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| order_ts | TIMESTAMP | NOT NULL |
| geo | VARCHAR(10) | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| subtotal_usd | DECIMAL(10,2) | NOT NULL |
| tax_usd | DECIMAL(10,2) | NOT NULL |
| delivery_fee_usd | DECIMAL(10,2) | NOT NULL |
| discount_usd | DECIMAL(10,2) | NOT NULL |
Sample data:
| delivery_fee_usd | discount_usd | geo | order_id | order_ts | status | subtotal_usd | tax_usd | user_id |
|---|---|---|---|---|---|---|---|---|
| 5 | 0 | MIA | 1 | 2025-08-11 10:00:00 | completed | 50 | 5 | 101 |
| 3 | 0 | MIA | 2 | 2025-08-12 14:00:00 | completed | 34 | 3 | 102 |
| 2 | 0 | ATL | 3 | 2025-08-13 16:00:00 | completed | 45 | 3 | 103 |
| 3 | 0 | NYC | 4 | 2025-08-14 09:30:00 | completed | 44 | 3 | 104 |
| 2 | 0 | MIA | 5 | 2025-08-18 12:00:00 | completed | 26 | 2 | 105 |
| 2 | 0 | ATL | 6 | 2025-08-19 18:00:00 | completed | 64 | 4 | 106 |
| 3 | 0 | NYC | 7 | 2025-08-20 11:00:00 | completed | 72 | 5 | 107 |
| 2 | 0 | NYC | 8 | 2025-08-21 17:00:00 | refunded | 40 | 3 | 108 |
| 1 | 0 | MIA | 9 | 2025-08-25 13:30:00 | completed | 17 | 2 | 109 |
| 0 | 0 | ATL | 10 | 2025-08-26 15:15:00 | completed | 9 | 1 | 110 |
| 0 | 0 | ATL | 11 | 2025-08-18 20:00:00 | cancelled | 100 | 5 | 111 |
Expected output
Up to three rows (top geos by absolute revenue change) when the latest complete week has a revenue drop of at least 4% vs the previous complete week. For the sample data, current_week_start is 2025-08-25, the last two complete weeks are 2025-08-11 and 2025-08-18, overall revenue drops from 200 to 180 (−10%), and the geos MIA, NYC, and ATL contribute to this delta.
| geo | latest_week_revenue_usd | latest_week_start_date | pct_change_vs_previous_geo | previous_week_revenue_usd | previous_week_start_date | revenue_delta_usd | share_of_total_delta_pct |
|---|---|---|---|---|---|---|---|
| MIA | 30 | 2025-08-18 | -70 | 100 | 2025-08-11 | -70 | 58.33 |
| NYC | 80 | 2025-08-18 | 60 | 50 | 2025-08-11 | 30 | 25 |
| ATL | 70 | 2025-08-18 | 40 | 50 | 2025-08-11 | 20 | 16.67 |
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.