Swiggy: Kitchens That Overshot the Promised Prep Time

Easy
955.

Swiggy: Kitchens That Overshot the Promised Prep Time

EasyJoinsDates

MySQL

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

ColumnType
restaurant_idint
restaurant_namevarchar
promised_prep_minutesint

Primary key: restaurant_id.

One row per restaurant, with the prep time it promises for every order.

Table: KitchenTicket

ColumnType
order_idint
restaurant_idint
accepted_atdatetime
food_ready_atdatetime

Primary key: order_id.

One row per order the kitchen accepted. food_ready_at is NULL when staff never pressed "food ready".

Example

The rows must come back in the order shown.

Restaurant

restaurant_idrestaurant_namepromised_prep_minutes
1Dosa Corner12
2Biryani Bowl House20
3Rolls Republic10

KitchenTicket

order_idrestaurant_idaccepted_atfood_ready_at
30112025-06-01 12:00:002025-06-01 12:15:00
30212025-06-01 12:10:002025-06-01 12:22:00
30322025-06-01 13:00:002025-06-01 13:26:00
30422025-06-01 13:05:00NULL
30532025-06-01 23:55:002025-06-02 00:08:00
30632025-06-01 20:00:002025-06-01 20:08:00

Output

order_idrestaurant_nameprep_minutesovershoot_minutes
303Biryani Bowl House266
301Dosa Corner153
305Rolls Republic133
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
restaurant_idint
restaurant_namevarchar
promised_prep_minutesint

Primary key: restaurant_id.

One row per restaurant, with the prep time it promises for every order.

Table: KitchenTicket

ColumnType
order_idint
restaurant_idint
accepted_atdatetime
food_ready_atdatetime

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_idrestaurant_namepromised_prep_minutes
1Dosa Corner12
2Biryani Bowl House20
3Rolls Republic10

KitchenTicket

order_idrestaurant_idaccepted_atfood_ready_at
30112025-06-01 12:00:002025-06-01 12:15:00
30212025-06-01 12:10:002025-06-01 12:22:00
30322025-06-01 13:00:002025-06-01 13:26:00
30422025-06-01 13:05:00NULL
30532025-06-01 23:55:002025-06-02 00:08:00
30632025-06-01 20:00:002025-06-01 20:08:00

Output

order_idrestaurant_nameprep_minutesovershoot_minutes
303Biryani Bowl House266
301Dosa Corner153
305Rolls Republic133

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_id

Another 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 →