← DoorDash Interview Insights

DoorDash·Data Scientist·Technical Phone Screen·Senior

Senior
Apr 2026Remote

Summary

DoorDash data science technical screen, heavy SQL focus with a food-delivery domain problem that had a lot of moving parts. Four interconnected tasks on the same schema, window functions required throughout, and edge cases baked into the prompt itself.

Questions Asked (4)

Q1

Given a food-delivery schema (orders, deliveries, complaints, restaurants), compute a daily cold delivery rate for the last 7 days. A delivery is 'cold' if food temp drops below 40°C at dropoff OR a cold_food complaint is filed within 2 hours of dropoff. Deduplicate so each order counts once even if both conditions trigger. Return date, delivered_orders, cold_deliveries, and cold_rate rounded to 3 decimals.

Product Analytics & MetricsData Modeling
Author's notes

The deduplication part is what got me initially.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and defining the cold delivery condition precisely, then outline a SQL query that joins orders, deliveries, and complaints, deduplicates orders, and aggregates daily metrics for the last 7 days. Emphasize the importance of handling edge cases like time zones, missing data, and ensuring each order is counted once.

Pro tip: Mention that you would validate the cold rate by checking a few sample orders manually and consider the business impact of false positives/negatives. Also, discuss how you might monitor this metric over time and investigate anomalies.

1. Clarify requirements and schema

Ask questions to confirm the definition of 'cold' (temperature threshold, complaint window), the time zone for daily aggregation, and the exact tables/columns available. Ensure you understand how orders, deliveries, and complaints relate.

2. Design the query logic

Plan a SQL query that joins orders to deliveries and complaints, flags cold deliveries based on temperature or complaint, and deduplicates orders using a CASE or DISTINCT. Use a subquery or CTE to isolate the last 7 days.

3. Handle deduplication and aggregation

Ensure each order is counted once by using a flag per order (e.g., MAX of cold condition) and then aggregate by date to compute delivered_orders, cold_deliveries, and cold_rate. Round the rate to 3 decimals.

4. Validate and interpret results

Sanity-check the output: verify counts, look for anomalies, and consider edge cases like orders with no delivery or complaints outside the window. Discuss how to present the metric to stakeholders.

Key Points to Mention

  • Definition of cold delivery: temperature < 40°C at dropoff OR cold_food complaint within 2 hours of dropoff.
  • Deduplication strategy: use a flag per order (e.g., MAX(cold_condition)) to ensure each order counts once.
  • Time zone consideration: align timestamps to the correct time zone for daily aggregation.
  • Handling missing data: decide how to treat orders with missing temperature or delivery time.
  • Performance: use appropriate indexes and filter early to only last 7 days.
  • Business context: discuss how this metric might be used and potential limitations.

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

Q2

For each restaurant, compute a 7-day rolling cold delivery rate ordered by date, covering 2025-08-26 through 2025-09-01. Dates where a restaurant had zero deliveries should still appear with delivered_orders=0 and a NULL rate.

Data ModelingProduct Analytics & Metrics
Author's notes

Zero-volume dates killed me here.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by defining the cold delivery rate as the proportion of delivered orders that were cold, then build a date spine for each restaurant covering the full week to ensure zero-delivery days appear. Use a window function to compute a 7-day rolling sum of cold orders and total delivered orders, handling division by zero to return NULL when there are no deliveries.

Pro tip: Clarify with the interviewer whether the rolling window should be based on calendar days or the last 7 days with data; in most business contexts, calendar days are expected, and zero-delivery days should be included as zeros in the numerator and denominator, not skipped.

1. Define the metric

Cold delivery rate = cold delivered orders / total delivered orders. Confirm that 'cold' is a status flag and that the denominator includes all delivered orders, not just cold ones.

2. Create a date-restaurant spine

Generate a row for every restaurant and every date in the range 2025-08-26 to 2025-09-01, even if there were no deliveries. Left join the aggregated daily delivery data to this spine.

3. Aggregate daily deliveries

For each restaurant and date, compute total delivered orders and cold delivered orders. Ensure zero-delivery days have 0 for both counts.

4. Compute 7-day rolling sums

Use a window function to sum cold orders and total orders over the current date and the preceding 6 days, partitioned by restaurant and ordered by date.

5. Calculate rate and handle NULLs

Divide the rolling cold sum by the rolling total sum. Use NULLIF or a CASE statement to return NULL when the denominator is zero, ensuring zero-delivery days show delivered_orders=0 and rate=NULL.

Key Points to Mention

  • Use of a date spine to ensure all dates and restaurants appear, even with zero deliveries.
  • Window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for the 7-day rolling window.
  • Handling division by zero with NULLIF or CASE to produce NULL rates.
  • Partitioning by restaurant and ordering by date to compute rolling metrics per restaurant.
  • Clarifying whether the rolling window is based on calendar days or days with data.
  • Ensuring that zero-delivery days contribute 0 to both numerator and denominator in the rolling sums.

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

Q3

Using the 7-day rolling cold rate as of 2025-09-01, rank restaurants with DENSE_RANK(), breaking ties by higher total delivered orders. Return the top 3 with restaurant name, delivered orders, cold rate, and rank.

Product Analytics & MetricsAlgorithms & Data Structures
Author's notes

Straightforward once the previous CTE was built.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the definition of the 7-day rolling cold rate and the tie-breaking rule. Then, write a SQL query that computes the rolling cold rate for each restaurant as of 2025-09-01, ranks them using DENSE_RANK() ordered by cold rate ascending and total delivered orders descending, and finally filters to the top 3 ranks.

Pro tip: Mention that you would validate the rolling window calculation by checking a few restaurants manually, and discuss how you'd handle edge cases like restaurants with no orders in the window.

1. Clarify definitions and assumptions

Confirm what 'cold rate' means (e.g., percentage of orders delivered cold) and how the 7-day rolling window is defined (e.g., including the current date, using order date). Also confirm that 'total delivered orders' refers to the same 7-day window.

2. Compute rolling cold rate and total orders

Use a window function to calculate the 7-day rolling cold rate and total delivered orders for each restaurant as of 2025-09-01. Ensure you handle date ranges correctly and aggregate per restaurant.

3. Apply DENSE_RANK with tie-breaking

Use DENSE_RANK() OVER (ORDER BY cold_rate ASC, total_delivered_orders DESC) to rank restaurants. This ensures that ties in cold rate are broken by higher total delivered orders, and the next rank is not skipped.

4. Filter top 3 and select columns

Filter the ranked results to only include rows where rank <= 3, and return the restaurant name, delivered orders, cold rate, and rank. Order the final output by rank for readability.

5. Validate and discuss edge cases

Mention potential edge cases such as restaurants with no orders in the window (cold rate undefined) and how you would handle them (e.g., exclude or treat as 0). Also, discuss performance considerations for large datasets.

Key Points to Mention

  • Definition of cold rate: typically (number of cold orders / total delivered orders) * 100.
  • 7-day rolling window: use date range [2025-08-26, 2025-09-01] inclusive.
  • DENSE_RANK() vs RANK(): DENSE_RANK does not skip ranks after ties.
  • Tie-breaking: order by cold rate ascending (lower is better) and total delivered orders descending.
  • Handling NULL cold rates: exclude restaurants with no orders or treat as 0 based on business rules.
  • Performance: use appropriate indexes and avoid unnecessary subqueries.

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

Q4

Flag couriers whose cold delivery rate z-score over the last 7 days exceeds +2 standard deviations relative to the full courier population. Use windowed AVG() and STDDEV_POP() across couriers. Return courier_id, deliveries in the period, cold rate, and z-score.

Product Analytics & MetricsRoot Cause Analysis
Author's notes

This one was genuinely tricky.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, compute each courier's 7-day cold delivery rate and delivery count using a windowed aggregation over the last 7 days. Then, calculate the population mean and standard deviation of those rates across all couriers, and flag couriers whose z-score exceeds +2. Finally, return the required fields for flagged couriers.

Pro tip: When using window functions, ensure you're computing the population standard deviation (STDDEV_POP) over the courier-level rates, not the raw delivery-level data. Also, consider setting a minimum delivery threshold to avoid flagging couriers with very few deliveries, as their rates can be noisy.

1. Define the 7-day window and aggregate per courier

Filter deliveries to the last 7 days and compute each courier's total deliveries and cold deliveries. Then calculate the cold delivery rate as cold_deliveries / total_deliveries.

2. Compute population statistics

Using the courier-level cold rates, calculate the population mean and population standard deviation (STDDEV_POP) across all couriers.

3. Calculate z-scores and flag outliers

For each courier, compute the z-score as (cold_rate - mean) / stddev. Flag couriers where z-score > 2.

4. Return required fields

Select courier_id, deliveries in the period, cold_rate, and z-score for the flagged couriers.

Key Points to Mention

  • Use of window functions (AVG() and STDDEV_POP()) over the courier population, not over time.
  • Definition of cold delivery rate: cold deliveries divided by total deliveries.
  • Importance of using population standard deviation (STDDEV_POP) as specified.
  • Handling of couriers with zero or very few deliveries (e.g., minimum threshold).
  • Time window: last 7 days relative to the current date.
  • Z-score threshold: +2 standard deviations.

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