Swiggy: Kitchens That Overshot the Promised Prep Time
Every restaurant promises a preparation time, which the app uses to time the delivery partner's arrival.
- Difficulty: Easy
- Topics: Joins, Dates
- Dialect: MySQL
- Problem: #955
Problem statement
Every restaurant promises a preparation time, which the app uses to time the delivery partner's arrival. When the kitchen runs late the partner waits at the counter, so the restaurant-ops team tracks each overshoot.
An order's prep time is the minutes from accepted_at to food_ready_at (times are recorded to the minute). Return order_id, restaurant_name, prep_minutes and overshoot_minutes (prep time minus the restaurant's promised_prep_minutes) for every order whose prep time is strictly longer than promised. Orders never marked ready (food_ready_at NULL) are left out. Order by overshoot_minutes descending, then order_id.
A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.
Tables
Table: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
| promised_prep_minutes | int |
Primary key: restaurant_id.
One row per restaurant, with the prep time it promises for every order.
Table: KitchenTicket
| Column | Type |
|---|---|
| order_id | int |
| restaurant_id | int |
| accepted_at | datetime |
| food_ready_at | datetime |
Primary key: order_id.
One row per order the kitchen accepted. food_ready_at is NULL when staff never pressed "food ready".
Examples
Example 1
Restaurant
| restaurant_id | restaurant_name | promised_prep_minutes |
|---|---|---|
| 1 | Dosa Corner | 12 |
| 2 | Biryani Bowl House | 20 |
| 3 | Rolls Republic | 10 |
KitchenTicket
| order_id | restaurant_id | accepted_at | food_ready_at |
|---|---|---|---|
| 301 | 1 | 2025-06-01 12:00:00 | 2025-06-01 12:15:00 |
| 302 | 1 | 2025-06-01 12:10:00 | 2025-06-01 12:22:00 |
| 303 | 2 | 2025-06-01 13:00:00 | 2025-06-01 13:26:00 |
| 304 | 2 | 2025-06-01 13:05:00 | NULL |
| 305 | 3 | 2025-06-01 23:55:00 | 2025-06-02 00:08:00 |
| 306 | 3 | 2025-06-01 20:00:00 | 2025-06-01 20:08:00 |
Output
| order_id | restaurant_name | prep_minutes | overshoot_minutes |
|---|---|---|---|
| 303 | Biryani Bowl House | 26 | 6 |
| 301 | Dosa Corner | 15 | 3 |
| 305 | Rolls Republic | 13 | 3 |
How to solve Swiggy: Kitchens That Overshot the Promised Prep Time
Two facts meet in each row: the order's actual prep time, computed from its two timestamps, and the restaurant's promise, stored on the restaurant. An inner join on restaurant_id brings them together, and TIMESTAMPDIFF(MINUTE, accepted_at, food_ready_at) measures the prep time. Using full timestamps rather than clock times matters for the ticket accepted at 23:55 and ready after midnight — the date rolls over and the difference stays correct.
The filter keeps prep times strictly greater than the promise; a kitchen that is exactly on time is not late. Tickets never marked ready have a NULL food_ready_at, so the difference is NULL and the comparison fails, dropping them without an extra condition (the alternative states IS NOT NULL explicitly and computes the minutes once in a derived table).
The output is ordered by the overshoot, biggest first, and order_id breaks ties so the order is fully determined. The work is one join through the restaurant's key.
Reference solution (MySQL)
SELECT k.order_id, r.restaurant_name,
TIMESTAMPDIFF(MINUTE, k.accepted_at, k.food_ready_at) AS prep_minutes,
TIMESTAMPDIFF(MINUTE, k.accepted_at, k.food_ready_at) - r.promised_prep_minutes AS overshoot_minutes
FROM KitchenTicket k
JOIN Restaurant r ON r.restaurant_id = k.restaurant_id
WHERE TIMESTAMPDIFF(MINUTE, k.accepted_at, k.food_ready_at) > r.promised_prep_minutes
ORDER BY overshoot_minutes DESC, k.order_idAnother way
SELECT order_id, restaurant_name, prep_minutes, prep_minutes - promised_prep_minutes AS overshoot_minutes
FROM (SELECT k.order_id, r.restaurant_name, r.promised_prep_minutes,
TIMESTAMPDIFF(MINUTE, k.accepted_at, k.food_ready_at) AS prep_minutes
FROM KitchenTicket k JOIN Restaurant r ON r.restaurant_id = k.restaurant_id
WHERE k.food_ready_at IS NOT NULL) t
WHERE prep_minutes > promised_prep_minutes
ORDER BY overshoot_minutes DESC, order_id← Swiggy: Dineout Restaurants With Repeat No-Shows · Swiggy: Share of Each Partner's Orders Delivered in a Batch →