Coding questions · DoorDash
Measure Late Deliveries and Identify Top Delayed Restaurants
This question evaluates proficiency in time-based aggregations, timestamp arithmetic, percentage calculations, and ranking/grouping using SQL or equivalent Python data-manipulation libraries.
orders
+----------+---------+--------------+---------------------+-------------------------+-----------------------+
| order_id | user_id | restaurant_id| created_at | estimated_delivery_at | actual_delivery_at |
+----------+---------+--------------+---------------------+-------------------------+-----------------------+
| 1 | 101 | 15 | 2023-07-10 12:00 | 2023-07-10 12:30 | 2023-07-10 12:40 |
| 2 | 102 | 17 | 2023-07-10 13:10 | 2023-07-10 13:45 | 2023-07-10 13:43 |
| 3 | 103 | 15 | 2023-07-11 10:05 | 2023-07-11 10:35 | 2023-07-11 11:00 |
| 4 | 104 | 18 | 2023-07-12 09:00 | 2023-07-12 09:25 | 2023-07-12 09:20 |
+----------+---------+--------------+---------------------+-------------------------+-----------------------+
Scenario
An on-demand food-delivery company wants to measure and monitor late deliveries.
Question
Write a SQL query that, for the last 7 days, returns each day’s total orders and the percentage that were delivered more than 10 minutes after estimated_delivery_at. Extend it to list the top 5 restaurants with the highest average delivery delay in that period.
Hints
Use DATE_TRUNC / DATE() for grouping, TIMESTAMPDIFF or equivalent to compute delay, and ORDER BY with LIMIT for ranking.
Daily late-delivery rate (last 7 days)
An on-demand food-delivery company wants to measure and monitor late deliveries.
For the 7-day period from 2025-05-26 through 2025-06-01 (inclusive), return one row per day with:
- the day (based on orders.created_at)
- total delivered orders that day (only count rows where BOTH estimated_delivery_at and actual_delivery_at are NOT NULL)
- the percentage of those delivered orders that were delivered more than 10 minutes after estimated_delivery_at
A delivery is considered "late" if actual_delivery_at > estimated_delivery_at + 10 minutes. Return the percentage as a number rounded to 2 decimals.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| restaurant_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| estimated_delivery_at | TIMESTAMP | |
| actual_delivery_at | TIMESTAMP |
Sample data:
| actual_delivery_at | created_at | estimated_delivery_at | order_id | restaurant_id | user_id |
|---|---|---|---|---|---|
| 2025-05-26 12:50:00 | 2025-05-26 12:00:00 | 2025-05-26 12:30:00 | 1 | 15 | 101 |
| 2025-05-26 13:52:00 | 2025-05-26 13:10:00 | 2025-05-26 13:45:00 | 2 | 17 | 102 |
| 2025-05-26 09:40:00 | 2025-05-26 09:00:00 | 2025-05-26 09:25:00 | 3 | 18 | 103 |
| 2025-05-27 10:41:00 | 2025-05-27 10:05:00 | 2025-05-27 10:30:00 | 4 | 15 | 104 |
| 2025-05-27 18:10:00 | 2025-05-27 17:20:00 | 2025-05-27 18:00:00 | 5 | 15 | 105 |
| 2025-05-27 12:05:00 | 2025-05-27 12:00:00 | 2025-05-27 12:20:00 | 6 | 19 | 106 |
| 2025-05-28 11:55:00 | 2025-05-28 11:00:00 | 2025-05-28 11:30:00 | 7 | 17 | 107 |
| 2025-05-28 14:24:00 | 2025-05-28 14:00:00 | 2025-05-28 14:15:00 | 8 | 18 | 108 |
| NULL | 2025-05-28 18:30:00 | 2025-05-28 19:00:00 | 9 | 20 | 109 |
| 2025-05-28 20:10:00 | 2025-05-28 19:30:00 | NULL | 10 | 21 | 110 |
| 2025-05-29 08:50:00 | 2025-05-29 08:00:00 | 2025-05-29 08:20:00 | 11 | 19 | 111 |
| 2025-05-29 12:05:00 | 2025-05-29 11:30:00 | 2025-05-29 12:00:00 | 12 | 18 | 112 |
| 2025-05-29 13:12:00 | 2025-05-29 12:20:00 | 2025-05-29 13:00:00 | 13 | 17 | 113 |
| 2025-05-30 09:13:00 | 2025-05-30 08:15:00 | 2025-05-30 09:00:00 | 14 | 15 | 114 |
| 2025-05-30 17:52:00 | 2025-05-30 17:00:00 | 2025-05-30 17:40:00 | 15 | 20 | 115 |
| 2025-05-30 21:09:00 | 2025-05-30 20:20:00 | 2025-05-30 21:00:00 | 16 | 21 | 116 |
| 2025-05-31 12:21:00 | 2025-05-31 11:40:00 | 2025-05-31 12:10:00 | 17 | 17 | 117 |
| 2025-05-31 15:08:00 | 2025-05-31 14:00:00 | 2025-05-31 15:00:00 | 18 | 19 | 118 |
| 2025-05-31 18:45:00 | 2025-05-31 18:00:00 | 2025-05-31 18:20:00 | 19 | 18 | 119 |
| 2025-06-01 10:58:00 | 2025-06-01 10:15:00 | 2025-06-01 11:00:00 | 20 | 15 | 120 |
| 2025-06-01 12:55:00 | 2025-06-01 12:00:00 | 2025-06-01 12:30:00 | 21 | 20 | 121 |
| 2025-06-01 13:20:00 | 2025-06-01 12:10:00 | 2025-06-01 13:00:00 | 22 | 21 | 122 |
| 2025-06-01 14:12:00 | 2025-06-01 13:40:00 | 2025-06-01 14:00:00 | 23 | 18 | 123 |
Expected output
One row per day in 2025-05-26 through 2025-06-01, showing delivered_order_count and late_percentage (>10 minutes late).
| day | pct_late_over_10_min | total_delivered_orders |
|---|---|---|
| 2025-05-26 | 66.67 | 3 |
| 2025-05-27 | 33.33 | 3 |
| 2025-05-28 | 50 | 2 |
| 2025-05-29 | 66.67 | 3 |
| 2025-05-30 | 66.67 | 3 |
| 2025-05-31 | 66.67 | 3 |
| 2025-06-01 | 75 | 4 |
Hints
- Filter to the exact 7-day window using a half-open interval: >= 2025-05-26 and < 2025-06-02.
- Use a conditional count (e.g., FILTER or SUM(CASE...)) to compute late orders, then divide by total.
Top 5 restaurants by highest average delivery delay (last 7 days)
Using the same 7-day period from 2025-05-26 through 2025-06-01 (inclusive), list the top 5 restaurants with the highest average delivery delay.
Only include delivered orders where BOTH estimated_delivery_at and actual_delivery_at are NOT NULL.
Define delivery_delay_minutes as the number of minutes between estimated_delivery_at and actual_delivery_at, but do not allow negative values (early deliveries count as 0 minutes delay): delivery_delay_minutes = GREATEST(actual_delivery_at - estimated_delivery_at, 0)
Return restaurant_id and avg_delay_minutes rounded to 2 decimals, ordered by avg_delay_minutes descending (break ties by restaurant_id ascending).
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| restaurant_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| estimated_delivery_at | TIMESTAMP | |
| actual_delivery_at | TIMESTAMP |
Sample data:
| actual_delivery_at | created_at | estimated_delivery_at | order_id | restaurant_id | user_id |
|---|---|---|---|---|---|
| 2025-05-26 12:50:00 | 2025-05-26 12:00:00 | 2025-05-26 12:30:00 | 1 | 15 | 101 |
| 2025-05-26 13:52:00 | 2025-05-26 13:10:00 | 2025-05-26 13:45:00 | 2 | 17 | 102 |
| 2025-05-26 09:40:00 | 2025-05-26 09:00:00 | 2025-05-26 09:25:00 | 3 | 18 | 103 |
| 2025-05-27 10:41:00 | 2025-05-27 10:05:00 | 2025-05-27 10:30:00 | 4 | 15 | 104 |
| 2025-05-27 18:10:00 | 2025-05-27 17:20:00 | 2025-05-27 18:00:00 | 5 | 15 | 105 |
| 2025-05-27 12:05:00 | 2025-05-27 12:00:00 | 2025-05-27 12:20:00 | 6 | 19 | 106 |
| 2025-05-28 11:55:00 | 2025-05-28 11:00:00 | 2025-05-28 11:30:00 | 7 | 17 | 107 |
| 2025-05-28 14:24:00 | 2025-05-28 14:00:00 | 2025-05-28 14:15:00 | 8 | 18 | 108 |
| NULL | 2025-05-28 18:30:00 | 2025-05-28 19:00:00 | 9 | 20 | 109 |
| 2025-05-28 20:10:00 | 2025-05-28 19:30:00 | NULL | 10 | 21 | 110 |
| 2025-05-29 08:50:00 | 2025-05-29 08:00:00 | 2025-05-29 08:20:00 | 11 | 19 | 111 |
| 2025-05-29 12:05:00 | 2025-05-29 11:30:00 | 2025-05-29 12:00:00 | 12 | 18 | 112 |
| 2025-05-29 13:12:00 | 2025-05-29 12:20:00 | 2025-05-29 13:00:00 | 13 | 17 | 113 |
| 2025-05-30 09:13:00 | 2025-05-30 08:15:00 | 2025-05-30 09:00:00 | 14 | 15 | 114 |
| 2025-05-30 17:52:00 | 2025-05-30 17:00:00 | 2025-05-30 17:40:00 | 15 | 20 | 115 |
| 2025-05-30 21:09:00 | 2025-05-30 20:20:00 | 2025-05-30 21:00:00 | 16 | 21 | 116 |
| 2025-05-31 12:21:00 | 2025-05-31 11:40:00 | 2025-05-31 12:10:00 | 17 | 17 | 117 |
| 2025-05-31 15:08:00 | 2025-05-31 14:00:00 | 2025-05-31 15:00:00 | 18 | 19 | 118 |
| 2025-05-31 18:45:00 | 2025-05-31 18:00:00 | 2025-05-31 18:20:00 | 19 | 18 | 119 |
| 2025-06-01 10:58:00 | 2025-06-01 10:15:00 | 2025-06-01 11:00:00 | 20 | 15 | 120 |
| 2025-06-01 12:55:00 | 2025-06-01 12:00:00 | 2025-06-01 12:30:00 | 21 | 20 | 121 |
| 2025-06-01 13:20:00 | 2025-06-01 12:10:00 | 2025-06-01 13:00:00 | 22 | 21 | 122 |
| 2025-06-01 14:12:00 | 2025-06-01 13:40:00 | 2025-06-01 14:00:00 | 23 | 18 | 123 |
Expected output
Top 5 restaurants by highest average non-negative delay (in minutes) over the 7-day period, rounded to 2 decimals.
| avg_delay_minutes | restaurant_id |
|---|---|
| 18.5 | 20 |
| 14.5 | 21 |
| 13.75 | 17 |
| 13.2 | 18 |
| 12.67 | 19 |
Hints
- Compute delay in minutes from the timestamp difference and clamp negative values to 0 with GREATEST().
- Aggregate by restaurant_id, then ORDER BY the average delay descending and LIMIT 5.