Coding questions · DoorDash
Write SQL for cuisine median delivery times
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.
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.
Compare Cuisine Delivery-Time Medians Across Two Windows
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.
Table restaurants
| column | type | constraints |
|---|---|---|
| restaurant_id | INTEGER | PRIMARY KEY |
| name | TEXT | NOT NULL |
| cuisine | TEXT | NOT NULL |
| is_ghost_kitchen | BOOLEAN | NOT NULL |
Sample data:
| 0 | 1 | 2 | 3 |
|---|---|---|---|
| 1 | Sushi A | Sushi | false |
| 2 | Pizza B | Pizza | false |
| 3 | Wings C | American | true |
| 4 | Curry D | Indian | false |
| 5 | Sushi Ghost | Sushi | true |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INTEGER | PRIMARY KEY |
| restaurant_id | INTEGER | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| status | TEXT | NOT NULL |
| subtotal | NUMERIC(10,2) | NOT NULL |
| city | TEXT | NOT NULL |
Sample data:
| 0 | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|
| 1001 | 1 | 2025-08-25 00:00:00 | completed | 31 | SF |
| 1002 | 1 | 2025-08-26 09:00:00 | completed | 32 | SF |
| 1003 | 1 | 2025-08-26 09:01:00 | completed | 33 | SF |
| 1004 | 1 | 2025-08-26 09:02:00 | completed | 34 | SF |
| 1005 | 1 | 2025-08-26 09:03:00 | completed | 35 | SF |
| 1006 | 1 | 2025-08-27 09:00:00 | completed | 36 | SF |
| 1007 | 1 | 2025-08-27 09:01:00 | completed | 37 | SF |
| 1008 | 1 | 2025-08-27 09:02:00 | completed | 38 | SF |
| 1009 | 1 | 2025-08-27 09:03:00 | completed | 39 | SF |
| 1010 | 1 | 2025-08-28 09:00:00 | completed | 40 | SF |
| 1011 | 1 | 2025-08-28 09:01:00 | completed | 41 | SF |
| 1012 | 1 | 2025-08-28 09:02:00 | completed | 42 | SF |
| 1013 | 1 | 2025-08-28 09:03:00 | completed | 43 | SF |
| 1014 | 1 | 2025-08-29 09:00:00 | completed | 44 | SF |
| 1015 | 1 | 2025-08-29 09:01:00 | completed | 45 | SF |
| 1016 | 1 | 2025-08-29 09:02:00 | completed | 46 | SF |
| 1017 | 1 | 2025-08-29 09:03:00 | completed | 47 | SF |
| 1018 | 1 | 2025-08-30 09:00:00 | completed | 48 | SF |
| 1019 | 1 | 2025-08-30 09:01:00 | completed | 49 | SF |
| 1020 | 1 | 2025-08-30 09:02:00 | completed | 50 | SF |
| 1021 | 1 | 2025-08-30 09:03:00 | completed | 51 | SF |
| 1022 | 1 | 2025-08-31 09:00:00 | completed | 52 | SF |
| 1023 | 1 | 2025-08-31 09:01:00 | completed | 53 | SF |
| 1024 | 1 | 2025-08-31 09:02:00 | completed | 54 | SF |
| 1025 | 1 | 2025-08-31 09:03:00 | completed | 55 | SF |
| 1026 | 1 | 2025-09-01 08:00:00 | completed | 56 | SF |
| 1027 | 1 | 2025-09-01 08:01:00 | completed | 57 | SF |
| 1028 | 1 | 2025-09-01 08:02:00 | completed | 58 | SF |
| 1029 | 1 | 2025-09-01 08:03:00 | completed | 59 | SF |
| 1030 | 1 | 2025-09-01 23:00:00 | completed | 60 | SF |
| 1101 | 1 | 2025-08-18 00:00:00 | completed | 41 | SF |
| 1102 | 1 | 2025-08-20 11:00:00 | completed | 42 | SF |
| 1103 | 1 | 2025-08-22 12:00:00 | completed | 43 | SF |
| 1104 | 1 | 2025-08-24 23:00:00 | completed | 44 | SF |
| 2001 | 2 | 2025-08-30 09:00:00 | completed | 21 | OAK |
| 2002 | 2 | 2025-08-30 09:01:00 | completed | 22 | OAK |
| 2003 | 2 | 2025-08-30 09:02:00 | completed | 23 | OAK |
| 2004 | 2 | 2025-08-30 09:03:00 | completed | 24 | OAK |
| 2005 | 2 | 2025-08-30 09:04:00 | completed | 25 | OAK |
| 2006 | 2 | 2025-08-30 09:05:00 | completed | 26 | OAK |
| 2007 | 2 | 2025-08-30 09:06:00 | completed | 27 | OAK |
| 2008 | 2 | 2025-08-30 09:07:00 | completed | 28 | OAK |
| 2009 | 2 | 2025-08-30 09:08:00 | completed | 29 | OAK |
| 2010 | 2 | 2025-08-30 09:09:00 | completed | 30 | OAK |
| 2011 | 2 | 2025-08-30 09:10:00 | completed | 31 | OAK |
| 2012 | 2 | 2025-08-30 09:11:00 | completed | 32 | OAK |
| 2013 | 2 | 2025-08-30 09:12:00 | completed | 33 | OAK |
| 2014 | 2 | 2025-08-30 09:13:00 | completed | 34 | OAK |
| 2015 | 2 | 2025-08-30 09:14:00 | completed | 35 | OAK |
| 2016 | 2 | 2025-08-30 09:15:00 | completed | 36 | OAK |
| 2017 | 2 | 2025-08-30 09:16:00 | completed | 37 | OAK |
| 2018 | 2 | 2025-08-30 09:17:00 | completed | 38 | OAK |
| 2019 | 2 | 2025-08-30 09:18:00 | completed | 39 | OAK |
| 2020 | 2 | 2025-08-30 09:19:00 | completed | 40 | OAK |
| 2021 | 2 | 2025-08-30 09:20:00 | completed | 41 | OAK |
| 2022 | 2 | 2025-08-30 09:21:00 | completed | 42 | OAK |
| 2023 | 2 | 2025-08-30 09:22:00 | completed | 43 | OAK |
| 2024 | 2 | 2025-08-30 09:23:00 | completed | 44 | OAK |
| 2025 | 2 | 2025-08-30 09:24:00 | completed | 45 | OAK |
| 2026 | 2 | 2025-08-30 09:25:00 | completed | 46 | OAK |
| 2027 | 2 | 2025-08-30 09:26:00 | completed | 47 | OAK |
| 2028 | 2 | 2025-08-30 09:27:00 | completed | 48 | OAK |
| 2029 | 2 | 2025-08-30 09:28:00 | completed | 49 | OAK |
| 3001 | 5 | 2025-08-29 12:00:00 | completed | 22 | SF |
| 3002 | 1 | 2025-08-29 12:01:00 | canceled | 23 | SF |
| 3003 | 1 | 2025-08-29 12:02:00 | completed | 24 | SF |
| 3004 | 1 | 2025-09-02 00:00:00 | completed | 25 | SF |
| 3005 | 1 | 2025-08-17 23:59:59 | completed | 26 | SF |
| 3006 | 1 | 2025-08-29 12:03:00 | completed | 27 | SF |
Table deliveries
| column | type | constraints |
|---|---|---|
| delivery_id | INTEGER | PRIMARY KEY |
| order_id | INTEGER | NOT NULL |
| pickup_time | TIMESTAMP | |
| dropoff_time | TIMESTAMP | |
| tip | NUMERIC(10,2) | |
| distance_miles | NUMERIC(6,2) |
Sample data:
| 0 | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|
| 9001 | 1001 | 2025-08-25 00:10:00 | 2025-08-25 00:21:00 | 4 | 2 |
| 9002 | 1002 | 2025-08-26 10:00:00 | 2025-08-26 10:12:00 | 4 | 2 |
| 9003 | 1003 | 2025-08-26 10:00:00 | 2025-08-26 10:13:00 | 4 | 2 |
| 9004 | 1004 | 2025-08-26 10:00:00 | 2025-08-26 10:14:00 | 4 | 2 |
| 9005 | 1005 | 2025-08-26 10:00:00 | 2025-08-26 10:15:00 | 4 | 2 |
| 9006 | 1006 | 2025-08-27 10:00:00 | 2025-08-27 10:16:00 | 4 | 2 |
| 9007 | 1007 | 2025-08-27 10:00:00 | 2025-08-27 10:17:00 | 4 | 2 |
| 9008 | 1008 | 2025-08-27 10:00:00 | 2025-08-27 10:18:00 | 4 | 2 |
| 9009 | 1009 | 2025-08-27 10:00:00 | 2025-08-27 10:19:00 | 4 | 2 |
| 9010 | 1010 | 2025-08-28 10:00:00 | 2025-08-28 10:20:00 | 4 | 2 |
| 9011 | 1011 | 2025-08-28 10:00:00 | 2025-08-28 10:21:00 | 4 | 2 |
| 9012 | 1012 | 2025-08-28 10:00:00 | 2025-08-28 10:22:00 | 4 | 2 |
| 9013 | 1013 | 2025-08-28 10:00:00 | 2025-08-28 10:23:00 | 4 | 2 |
| 9014 | 1014 | 2025-08-29 10:00:00 | 2025-08-29 10:24:00 | 4 | 2 |
| 9015 | 1015 | 2025-08-29 10:00:00 | 2025-08-29 10:25:00 | 4 | 2 |
| 9016 | 1016 | 2025-08-29 10:00:00 | 2025-08-29 10:26:00 | 4 | 2 |
| 9017 | 1017 | 2025-08-29 10:00:00 | 2025-08-29 10:27:00 | 4 | 2 |
| 9018 | 1018 | 2025-08-30 10:00:00 | 2025-08-30 10:28:00 | 4 | 2 |
| 9019 | 1019 | 2025-08-30 10:00:00 | 2025-08-30 10:29:00 | 4 | 2 |
| 9020 | 1020 | 2025-08-30 10:00:00 | 2025-08-30 10:30:00 | 4 | 2 |
| 9021 | 1021 | 2025-08-30 10:00:00 | 2025-08-30 10:31:00 | 4 | 2 |
| 9022 | 1022 | 2025-08-31 10:00:00 | 2025-08-31 10:32:00 | 4 | 2 |
| 9023 | 1023 | 2025-08-31 10:00:00 | 2025-08-31 10:33:00 | 4 | 2 |
| 9024 | 1024 | 2025-08-31 10:00:00 | 2025-08-31 10:34:00 | 4 | 2 |
| 9025 | 1025 | 2025-08-31 10:00:00 | 2025-08-31 10:35:00 | 4 | 2 |
| 9026 | 1026 | 2025-09-01 10:00:00 | 2025-09-01 10:36:00 | 4 | 2 |
| 9027 | 1027 | 2025-09-01 10:00:00 | 2025-09-01 10:37:00 | 4 | 2 |
| 9028 | 1028 | 2025-09-01 10:00:00 | 2025-09-01 10:38:00 | 4 | 2 |
| 9029 | 1029 | 2025-09-01 10:00:00 | 2025-09-01 10:39:00 | 4 | 2 |
| 9030 | 1030 | 2025-09-01 23:10:00 | 2025-09-01 23:50:00 | 4 | 2 |
| 9101 | 1101 | 2025-08-18 00:10:00 | 2025-08-18 00:20:00 | 4 | 2 |
| 9102 | 1102 | 2025-08-20 11:10:00 | 2025-08-20 11:30:00 | 4 | 2 |
| 9103 | 1103 | 2025-08-22 12:10:00 | 2025-08-22 12:40:00 | 4 | 2 |
| 9104 | 1104 | 2025-08-24 23:10:00 | 2025-08-24 23:50:00 | 4 | 2 |
| 9201 | 2001 | 2025-08-30 10:00:00 | 2025-08-30 10:18:00 | 3 | 3 |
| 9202 | 2002 | 2025-08-30 10:01:00 | 2025-08-30 10:19:00 | 3 | 3 |
| 9203 | 2003 | 2025-08-30 10:02:00 | 2025-08-30 10:20:00 | 3 | 3 |
| 9204 | 2004 | 2025-08-30 10:03:00 | 2025-08-30 10:21:00 | 3 | 3 |
| 9205 | 2005 | 2025-08-30 10:04:00 | 2025-08-30 10:22:00 | 3 | 3 |
| 9206 | 2006 | 2025-08-30 10:05:00 | 2025-08-30 10:23:00 | 3 | 3 |
| 9207 | 2007 | 2025-08-30 10:06:00 | 2025-08-30 10:24:00 | 3 | 3 |
| 9208 | 2008 | 2025-08-30 10:07:00 | 2025-08-30 10:25:00 | 3 | 3 |
| 9209 | 2009 | 2025-08-30 10:08:00 | 2025-08-30 10:26:00 | 3 | 3 |
| 9210 | 2010 | 2025-08-30 10:09:00 | 2025-08-30 10:27:00 | 3 | 3 |
| 9211 | 2011 | 2025-08-30 10:10:00 | 2025-08-30 10:28:00 | 3 | 3 |
| 9212 | 2012 | 2025-08-30 10:11:00 | 2025-08-30 10:29:00 | 3 | 3 |
| 9213 | 2013 | 2025-08-30 10:12:00 | 2025-08-30 10:30:00 | 3 | 3 |
| 9214 | 2014 | 2025-08-30 10:13:00 | 2025-08-30 10:31:00 | 3 | 3 |
| 9215 | 2015 | 2025-08-30 10:14:00 | 2025-08-30 10:32:00 | 3 | 3 |
| 9216 | 2016 | 2025-08-30 10:15:00 | 2025-08-30 10:33:00 | 3 | 3 |
| 9217 | 2017 | 2025-08-30 10:16:00 | 2025-08-30 10:34:00 | 3 | 3 |
| 9218 | 2018 | 2025-08-30 10:17:00 | 2025-08-30 10:35:00 | 3 | 3 |
| 9219 | 2019 | 2025-08-30 10:18:00 | 2025-08-30 10:36:00 | 3 | 3 |
| 9220 | 2020 | 2025-08-30 10:19:00 | 2025-08-30 10:37:00 | 3 | 3 |
| 9221 | 2021 | 2025-08-30 10:20:00 | 2025-08-30 10:38:00 | 3 | 3 |
| 9222 | 2022 | 2025-08-30 10:21:00 | 2025-08-30 10:39:00 | 3 | 3 |
| 9223 | 2023 | 2025-08-30 10:22:00 | 2025-08-30 10:40:00 | 3 | 3 |
| 9224 | 2024 | 2025-08-30 10:23:00 | 2025-08-30 10:41:00 | 3 | 3 |
| 9225 | 2025 | 2025-08-30 10:24:00 | 2025-08-30 10:42:00 | 3 | 3 |
| 9226 | 2026 | 2025-08-30 10:25:00 | 2025-08-30 10:43:00 | 3 | 3 |
| 9227 | 2027 | 2025-08-30 10:26:00 | 2025-08-30 10:44:00 | 3 | 3 |
| 9228 | 2028 | 2025-08-30 10:27:00 | 2025-08-30 10:45:00 | 3 | 3 |
| 9229 | 2029 | 2025-08-30 10:28:00 | 2025-08-30 10:46:00 | 3 | 3 |
| 9301 | 3001 | 2025-08-29 13:00:00 | 2025-08-29 13:14:00 | 2 | 2 |
| 9302 | 3002 | 2025-08-29 13:01:00 | 2025-08-29 13:16:00 | NULL | 2 |
| 9303 | 3003 | 2025-08-29 13:02:00 | 2025-08-29 13:18:00 | 2 | 10 |
| 9304 | 3004 | 2025-09-02 00:10:00 | 2025-09-02 00:27:00 | 2 | 2 |
| 9305 | 3005 | 2025-08-18 00:10:00 | 2025-08-18 00:28:00 | 2 | 2 |
| 9306 | 3006 | NULL | NULL | NULL | 2 |
Expected output
| cuisine | recent_median_min | prev_median_min | pct_change | n_recent |
|---|---|---|---|---|
| Sushi | 25.5 | 25 | 0.02 | 30 |
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.
- Apply the recent-delivery threshold with a filtered COUNT in HAVING, then calculate pct_change from the two medians.