Measure Late Deliveries and Identify Top Delayed Restaurants

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at DoorDash.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at DoorDash during technical interviews.

Question

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.

PracHub · Accepted Answer

This question evaluates proficiency in time-based aggregations, timestamp arithmetic, percentage calculations, and ranking/grouping using SQL or equivalent Python data-manipulation libraries.

Quick Overview

Quick Overview