← DoorDash Interview Insights

DoorDash·Data Scientist·Take-home Assignment·Senior

Senior
May 2026Remote

Summary

DoorDash data science take-home that was way more involved than I expected. One massive SQL problem covering window functions, rolling aggregates, policy simulation, and multi-CTE output. Took me a few hours and I'm still not sure I got the rolling window right.

Questions Asked (1)

Q1

Write a single SQL query (CTEs allowed) that, for orders in the last 30 days: computes lateness in minutes using GREATEST and EXTRACT EPOCH, ranks orders within each city and delivery date using PERCENT_RANK, computes a rolling 7-day cold-food refund cost per order at the store-day level, simulates a proposed tiered refund policy (0% under 10 min late, 50% for 10-29 min, 100% for 30+ min) with a $50 cap compared to the current 100%-of-subtotal policy, and outputs both city-level aggregate savings and the top 5% store-days by current refund cost per order.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs
Author's notes

The lateness calculation itself was fine, GREATEST(0, ...) to floor at zero is pretty standard.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the complex query into logical CTEs: first filter orders from the last 30 days and compute lateness in minutes using GREATEST and EXTRACT EPOCH, then add window functions for PERCENT_RANK and rolling 7-day refund cost, then simulate the tiered refund policy with a $50 cap and compare to current policy, and finally aggregate savings by city and identify top 5% store-days. Use clear aliases and comment each CTE to show structure and avoid errors.

Pro tip: Mention that the rolling 7-day refund cost should be computed at the store-day level using a window frame like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, and clarify that PERCENT_RANK is used to rank orders within each city and delivery date, which helps identify the top 5% store-days by current refund cost per order.

1. Filter and compute lateness

Filter orders from the last 30 days and compute lateness in minutes using GREATEST(0, EXTRACT(EPOCH FROM (delivered_at - promised_at))/60).

2. Add window functions

Use PERCENT_RANK() OVER (PARTITION BY city, delivery_date ORDER BY lateness_minutes) to rank orders, and compute rolling 7-day cold-food refund cost per order at store-day level using a window frame.

3. Simulate refund policies

Calculate current refund (100% of subtotal) and proposed tiered refund (0% under 10 min, 50% for 10-29 min, 100% for 30+ min) with a $50 cap, then compute savings as the difference.

4. Aggregate and output

Aggregate savings by city and identify top 5% store-days by current refund cost per order using the PERCENT_RANK or a threshold.

Key Points to Mention

  • Use of GREATEST to ensure non-negative lateness and EXTRACT EPOCH for precise minute calculation.
  • PERCENT_RANK for ranking orders within city and delivery date, and its use in identifying top 5% store-days.
  • Rolling 7-day window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for cold-food refund cost.
  • Tiered refund policy logic with CASE WHEN and LEAST for the $50 cap.
  • Comparison of proposed vs current policy to compute savings, and aggregation at city level.
  • Handling of edge cases like nulls, time zones, and ensuring the query is efficient with proper indexing.

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