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.
Quick Answer: 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.