Courier Wait Time Analysis
ASKED IN INTERVIEWDoor Dash
Problem Statement
To minimize Dasher frustration and improve delivery speed, DoorDash tracks the time a courier spends waiting at a merchant.
Write an SQL query to find the average wait time in minutes for each restaurant.
Rules:
- Only consider orders where the courier arrived before the order was ready (order_ready_time > courier_arrival_time).
- Wait time is defined as the difference between courier_arrival_time and order_ready_time.
- Calculate the average wait time per restaurant_id.
- Output columns: restaurant_id, avg_wait_minutes.
- Round avg_wait_minutes to 2 decimal places.
- Only include restaurants where the average wait time is greater than 5 minutes.
- Results must be returned in descending order of avg_wait_minutes.
Table Schema:
- DeliveryLogs: delivery_id, restaurant_id, courier_id, courier_arrival_time (TIMESTAMP), order_ready_time (TIMESTAMP).
Tests your understanding of
DateTime Arithmetic, Aggregation, Filtering and EXTRACT
Input Tables
DeliveryLogs
| restaurant_id(INTEGER) | courier_arrival_time(TIMESTAMP) | order_ready_time(TIMESTAMP) |
|---|---|---|
| 10 | 2026-02-13 12:00:00 | 2026-02-13 12:15:00 |
| 10 | 2026-02-13 12:30:00 | 2026-02-13 12:40:00 |
| 11 | 2026-02-13 13:00:00 | 2026-02-13 13:04:00 |
| 12 | 2026-02-13 14:00:00 | 2026-02-13 14:20:00 |
| 12 | 2026-02-13 14:30:00 | 2026-02-13 14:32:00 |
| 10 | 2026-02-13 15:00:00 | 2026-02-13 14:55:00 |
Expected Output
| restaurant_id(INTEGER) | avg_wait_minutes(DECIMAL) |
|---|---|
| 10 | 12.5 |
| 12 | 11 |
Tags
MediumASKED IN INTERVIEWDateTime ArithmeticAggregationFilteringEXTRACT
15-20 min
48%