Measure Late Deliveries and Identify Top Delayed Restaurants
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency in time-based aggregations, timestamp arithmetic, percentage calculations, and ranking/grouping using SQL or equivalent Python data-manipulation libraries.
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.
Tables
orders(order_id INT, user_id INT, restaurant_id INT, created_at TIMESTAMP, estimated_delivery_at TIMESTAMP, actual_delivery_at TIMESTAMP)
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).
Tables
orders(order_id INT, user_id INT, restaurant_id INT, created_at TIMESTAMP, estimated_delivery_at TIMESTAMP, actual_delivery_at TIMESTAMP)
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.
Community answers
Answer by SS
For last 7 days
Each day total orders
Orders % more than 10 min from estimated dleivery time
With daily as
(Select date(created_At) as date , count(distinct order_id) as Total_orders ,((count(distinct case when Extract(Epoch from (actual_delivery_At - estimated_Delivery_At))/60 > 10 then order_id else NULL end) 1.00)/count(distinct order_id) ) * 100 as perc_order
from orders
where date(Created_at) between current_Date and current_Date - interval '7 days'
group by 1)
Select restaurant_id , Avg(Extract(Epoch from (actual_delivery_At - estimated_Delivery_At))/60) as avg_Delivery_time
from orders
where date(Created_at) between current_Date and current_Date - interval '7 days'
group by1
order by 2 desc
limit 5