Katkat
Back

Aggregate weekly revenue and attribute 4% drop

InstacartData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.