← Instacart Interview Insights

Instacart·Data Scientist·Technical Phone Screen·Senior

Senior
Jun 2026

Summary

Instacart data science technical screen, one question but it was a beast. Multi-part SQL problem that starts reasonable and then keeps adding requirements until you're basically writing a mini analytics pipeline from scratch.

Questions Asked (1)

Q1

Given an orders table with timestamps, geo, status, and revenue components, write SQL to: (A) compute weekly revenue by ISO week excluding cancelled and refunded orders, where revenue equals subtotal plus tax plus delivery fee minus discount; (B) calculate the percent change between the last two complete weeks relative to the latest timestamp in the data; and (C) if the most recent complete week shows a drop of 4% or more versus the prior week, return the top 3 geos driving that decline, each with their share of the total revenue delta and their own week-over-week percent change.

Product Analytics & MetricsData ModelingRoot Cause Analysis
Author's notes

Part A was fine, date_trunc to Monday and filter on status = completed, standard stuff.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into three clear parts: first, compute weekly revenue by filtering out cancelled/refunded orders and aggregating revenue components by ISO week; second, identify the last two complete weeks relative to the latest timestamp and calculate the percent change; third, if the drop is 4% or more, attribute the decline to geos by computing each geo's revenue delta and its share of the total delta, then rank and return the top 3.

Pro tip: Always clarify how 'complete week' is defined relative to the latest timestamp—e.g., whether the latest timestamp is in the current week and should be excluded—and explicitly state your assumptions about ISO week boundaries and time zones.

1. Define revenue and filter orders

Calculate revenue as subtotal + tax + delivery_fee - discount, and exclude orders with status 'cancelled' or 'refunded'. Ensure you handle NULLs appropriately.

2. Aggregate weekly revenue

Use ISO week functions (e.g., EXTRACT(ISOYEAR FROM timestamp), EXTRACT(WEEK FROM timestamp)) to group revenue by week. Sum revenue per week.

3. Identify last two complete weeks

Find the latest timestamp in the data, determine the most recent complete ISO week (excluding the current partial week), and select the two most recent complete weeks.

4. Compute percent change and check threshold

Calculate the percent change between the last two complete weeks. If the drop is 4% or more, proceed to geo-level analysis.

5. Attribute decline to top geos

For the two weeks, compute revenue per geo, calculate each geo's revenue delta (current week - prior week), its share of the total revenue delta, and its own week-over-week percent change. Rank geos by absolute revenue decline and return the top 3.

Key Points to Mention

  • Use of ISO week functions (e.g., EXTRACT(ISOYEAR FROM timestamp), EXTRACT(WEEK FROM timestamp)) to ensure correct week grouping across year boundaries.
  • Filtering out cancelled and refunded orders using a WHERE clause on status.
  • Revenue calculation: subtotal + tax + delivery_fee - discount, with attention to NULL handling (e.g., COALESCE).
  • Definition of 'complete week' relative to the latest timestamp: exclude the current partial week and possibly the week containing the latest timestamp if it's incomplete.
  • Percent change formula: (current_week_revenue - prior_week_revenue) / prior_week_revenue * 100, and threshold check for >= 4% drop.
  • Geo-level attribution: compute each geo's revenue delta, share of total delta (geo_delta / total_delta), and week-over-week percent change, then order by absolute delta descending and limit to 3.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.