Write SQL for cuisine median delivery times
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Use SQL to answer the following. Assume ANSI SQL with window functions and percentile functions available. Treat “today” as 2025-09-01 (inclusive). Compute cuisine-level performance.
Schema and tiny sample data (for illustration only):
restaurants(restaurant_id INT, name TEXT, cuisine TEXT, is_ghost_kitchen BOOLEAN)
+---------------+------------+---------+-------------------+
| restaurant_id | name | cuisine | is_ghost_kitchen |
+---------------+------------+---------+-------------------+
| 1 | Sushi A | Sushi | false |
| 2 | Pizza B | Pizza | false |
| 3 | Wings C | American| true |
| 4 | Curry D | Indian | false |
+---------------+------------+---------+-------------------+
orders(order_id INT, restaurant_id INT, created_at TIMESTAMP, status TEXT, subtotal NUMERIC, city TEXT)
+----------+---------------+---------------------+-----------+----------+----------+
| order_id | restaurant_id | created_at | status | subtotal | city |
+----------+---------------+---------------------+-----------+----------+----------+
| 101 | 1 | 2025-08-25 18:02:00 | completed | 42.00 | SF |
| 102 | 2 | 2025-08-26 19:10:00 | completed | 28.50 | SF |
| 103 | 3 | 2025-08-27 12:05:00 | completed | 19.99 | SJ |
| 104 | 2 | 2025-08-28 20:14:00 | canceled | 31.00 | SF |
| 105 | 4 | 2025-08-31 11:40:00 | completed | 22.00 | OAK |
| 106 | 1 | 2025-09-01 13:22:00 | completed | 35.00 | SF |
+----------+---------------+---------------------+-----------+----------+----------+
deliveries(delivery_id INT, order_id INT, pickup_time TIMESTAMP, dropoff_time TIMESTAMP, tip NUMERIC, distance_miles NUMERIC)
+-------------+----------+---------------------+---------------------+------+----------------+
| delivery_id | order_id | pickup_time | dropoff_time | tip | distance_miles |
+-------------+----------+---------------------+---------------------+------+----------------+
| 9001 | 101 | 2025-08-25 18:18:00 | 2025-08-25 18:36:00 | 6.00 | 3.2 |
| 9002 | 102 | 2025-08-26 19:22:00 | 2025-08-26 19:52:00 | 5.00 | 7.5 |
| 9003 | 103 | 2025-08-27 12:20:00 | 2025-08-27 12:33:00 | 3.00 | 2.1 |
| 9004 | 104 | NULL | NULL | NULL | NULL |
| 9005 | 105 | 2025-08-31 11:55:00 | 2025-08-31 12:28:00 | 4.00 | 12.0 |
| 9006 | 106 | 2025-09-01 13:35:00 | 2025-09-01 13:55:00 | 2.00 | 4.0 |
+-------------+----------+---------------------+---------------------+------+----------------+
Task: Write a single SQL query that returns, for each cuisine: (a) median delivery_time_minutes = TIMESTAMP_DIFF(dropoff_time, pickup_time, MINUTE) for completed orders with distance_miles < 10 in the last 7 full days including 2025-09-01 (window A: 2025-08-25 to 2025-09-01), excluding ghost kitchens; (b) the same median for the preceding 7-day window (window B: 2025-08-18 to 2025-08-24); (c) pct_change = (A − B)/NULLIF(B,0); and (d) include only cuisines with at least 30 qualifying deliveries in window A. Output columns: cuisine, recent_median_min, prev_median_min, pct_change, n_recent. Assume UTC timestamps and that canceled orders have no delivery. Do not use temporary tables; use CTEs or subqueries only.
Overview: This question evaluates SQL proficiency in computing cuisine-level delivery performance metrics, including time-window filtering, joins, window/percentile functions for median aggregation, null handling, and percent-change calculations.
Read the full DoorDash Data Scientist interview experience this question came from
Using PostgreSQL, compute delivery-time performance by cuisine for the two explicit UTC windows in this problem: the recent window is 2025-08-25 00:00:00 inclusive through 2025-09-02 00:00:00 exclusive (so it includes all of 2025-09-01), and the preceding window is 2025-08-18 00:00:00 inclusive through 2025-08-25 00:00:00 exclusive. A qualifying delivery belongs to a completed order, has non-NULL pickup and dropoff timestamps, has distance_miles < 10, and comes from a restaurant that is not a ghost kitchen. Define delivery_time_minutes as the elapsed seconds from pickup_time to dropoff_time divided by 60. Return one row per cuisine with: cuisine, recent_median_min, prev_median_min, pct_change = (recent_median_min - prev_median_min) / NULLIF(prev_median_min, 0), and n_recent. Include only cuisines with at least 30 qualifying deliveries in the recent window. If a qualifying cuisine has no preceding-window deliveries, its preceding median and percentage change should be NULL. Order by cuisine ascending. Use one read-only query with CTEs or subqueries and no temporary tables.
Tables
restaurants(restaurant_id INTEGER, name TEXT, cuisine TEXT, is_ghost_kitchen BOOLEAN)
orders(order_id INTEGER, restaurant_id INTEGER, created_at TIMESTAMP, status TEXT, subtotal NUMERIC(10,2), city TEXT)
deliveries(delivery_id INTEGER, order_id INTEGER, pickup_time TIMESTAMP, dropoff_time TIMESTAMP, tip NUMERIC(10,2), distance_miles NUMERIC(6,2))
Hints
- Filter completed, non-ghost, under-10-mile deliveries with usable timestamps before aggregating.
- PostgreSQL's PERCENTILE_CONT(0.5) WITHIN GROUP computes a continuous median; FILTER can apply each date window to the same grouped cuisine.
Community answers
Answer by jimmyp
WITH delivery_metrics AS (
SELECT
r.cuisine,
TIMESTAMP_DIFF(d.dropoff_time, d.pickup_time, MINUTE) AS delivery_time_minutes,
CAST(o.created_at AS DATE) AS order_date
FROM orders o
JOIN restaurants r ON o.restaurant_id = r.restaurant_id
JOIN deliveries d ON o.order_id = d.order_id
WHERE o.status = 'completed'
AND d.distance_miles < 10
AND r.is_ghost_kitchen = FALSE
),
cuisine_medians AS (
SELECT
cuisine,
-- Window A Medians
PERCENTILE_CONT(0.5) WITHIN GROUP (
ORDER BY CASE WHEN order_date BETWEEN '2025-08-25' AND '2025-09-01' THEN delivery_time_minutes END
) AS median_delivery_time_A,
-- Window B Medians
PERCENTILE_CONT(0.5) WITHIN GROUP (
ORDER BY CASE WHEN order_date BETWEEN '2025-08-18' AND '2025-08-24' THEN delivery_time_minutes END
) AS median_delivery_time_B,
-- Count for Window A filter
COUNT(CASE WHEN order_date BETWEEN '2025-08-25' AND '2025-09-01' THEN 1 END) AS qualifying_deliveries_A
FROM delivery_metrics
GROUP BY cuisine
)
SELECT
cuisine,
median_delivery_time_A,
median_delivery_time_B,
(median_delivery_time_A - median_delivery_time_B) / NULLIF(median_delivery_time_B, 0) * 100.0 AS pct_change
FROM cuisine_medians
WHERE qualifying_deliveries_A >= 30;