← DoorDash Interview Insights

DoorDash·Data Scientist·Technical Phone Screen·Senior

Senior
Jun 2026Remote

Summary

DoorDash data science take-home style question, one big SQL problem about cuisine-level delivery performance with CTEs, window functions, and percentile math. Pretty gnarly for a single question but that's kind of their thing.

Questions Asked (1)

Q1

Write a single SQL query (CTEs or subqueries only, no temp tables) that computes, per cuisine: the median delivery time in minutes for completed orders under 10 miles in the most recent 7-day window, the same median for the prior 7-day window, the percent change between the two, and the count of qualifying deliveries. Exclude ghost kitchens, canceled orders, and any cuisine with fewer than 30 qualifying deliveries in the recent window.

Product Analytics & MetricsData Modeling
Author's notes

This one took me a while to untangle.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by defining the two 7-day windows relative to the latest order date, then filter to completed, non-ghost-kitchen orders under 10 miles. Use a CTE to compute per-cuisine medians for each window via PERCENTILE_CONT or a window-function rank approach, then join and calculate percent change while applying the 30-delivery threshold on the recent window.

Pro tip: Explicitly state your median method (e.g., PERCENTILE_CONT(0.5) WITHIN GROUP) and note that it interpolates for even counts, which is standard for delivery-time metrics; also clarify how you handle ties in the rank-based alternative to avoid off-by-one errors.

1. Define windows and base filters

Anchor the recent window to the max order date (or CURRENT_DATE) and define the prior window as the preceding 7 days. Apply filters for completed status, distance < 10 miles, and non-ghost-kitchen in a base CTE.

2. Compute per-cuisine medians per window

Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY delivery_minutes) grouped by cuisine and window, or a rank-based approach with ROW_NUMBER/AVG to handle ties. Keep the two windows separate or unioned with a window label.

3. Join windows and apply threshold

Join recent and prior medians on cuisine (use LEFT JOIN to keep recent cuisines), then filter to cuisines with at least 30 qualifying deliveries in the recent window.

4. Calculate percent change and final output

Compute percent change as (recent_median - prior_median) / prior_median * 100, handling NULL prior medians. Select cuisine, both medians, percent change, and recent delivery count.

Key Points to Mention

  • Use of PERCENTILE_CONT(0.5) WITHIN GROUP for exact median, or a rank-based method with ROW_NUMBER and AVG for tie handling.
  • Defining the 7-day windows relative to the latest order date to avoid empty windows if data is not current.
  • Filtering ghost kitchens via a NOT EXISTS or LEFT JOIN on a ghost_kitchen flag, and excluding canceled orders via status = 'completed'.
  • Applying the 30-delivery threshold only on the recent window, and using LEFT JOIN to retain cuisines with no prior data.
  • Handling NULL or zero prior medians in percent change calculation (e.g., NULLIF or CASE).
  • Ensuring distance filter uses miles and is applied before aggregation to avoid skewing medians.

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