Swiggy: Kitchens Slowing Down Three Weeks Running

Hard
970.

Swiggy: Kitchens Slowing Down Three Weeks Running

One slow week can be a staff shortage; a kitchen whose average prep time rises week after week is a trend the account manager should call about.

Weeks run Monday to Sunday. A restaurant's weekly prep time is the average, over its orders accepted that week, of the minutes from accepted_at to ready_at. Find every time a restaurant's weekly average went up strictly two weeks in a row across three consecutive calendar weeks — a week with no orders breaks the chain. Return restaurant_name and week_start (the Monday of the third week) for every such triple; overlapping triples are all reported. Order by restaurant_name, then week_start.

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

Primary key: restaurant_id.

One row per restaurant; names are unique.

Table: KitchenTicket

ColumnType
order_idint
restaurant_idint
accepted_atdatetime
ready_atdatetime

Primary key: order_id.

One row per order the kitchen prepared; times are recorded to the minute and an order is ready the same day it was accepted.

Example

The rows must come back in the order shown.

Restaurant

restaurant_idrestaurant_name
1Biryani Bowl House
2Dosa Corner
3Momo Hub

KitchenTicket

order_idrestaurant_idaccepted_atready_at
112025-06-03 12:00:002025-06-03 12:15:00
212025-06-09 12:00:002025-06-09 12:16:00
312025-06-15 20:00:002025-06-15 20:18:00
412025-06-18 13:00:002025-06-18 13:20:00
512025-06-25 13:00:002025-06-25 13:22:00
622025-06-04 09:00:002025-06-04 09:10:00
722025-06-08 09:00:002025-06-08 09:12:00
822025-06-24 09:00:002025-06-24 09:14:00
922025-07-01 09:00:002025-07-01 09:16:00
1032025-06-02 18:00:002025-06-02 18:12:00
2 more rows

Output

restaurant_nameweek_start
Biryani Bowl House2025-06-16
Biryani Bowl House2025-06-23
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 Slowing Down Three Weeks Running

One slow week can be a staff shortage; a kitchen whose average prep time rises week after week is a trend the account manager should call about.

  • Difficulty: Hard
  • Topics: Window Functions, Dates, Aggregation
  • Dialect: MySQL
  • Problem: #970

Problem statement

One slow week can be a staff shortage; a kitchen whose average prep time rises week after week is a trend the account manager should call about.

Weeks run Monday to Sunday. A restaurant's weekly prep time is the average, over its orders accepted that week, of the minutes from accepted_at to ready_at. Find every time a restaurant's weekly average went up strictly two weeks in a row across three consecutive calendar weeks — a week with no orders breaks the chain. Return restaurant_name and week_start (the Monday of the third week) for every such triple; overlapping triples are all reported. Order by restaurant_name, then week_start.

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

Primary key: restaurant_id.

One row per restaurant; names are unique.

Table: KitchenTicket

ColumnType
order_idint
restaurant_idint
accepted_atdatetime
ready_atdatetime

Primary key: order_id.

One row per order the kitchen prepared; times are recorded to the minute and an order is ready the same day it was accepted.

Examples

Example 1

Restaurant

restaurant_idrestaurant_name
1Biryani Bowl House
2Dosa Corner
3Momo Hub

KitchenTicket

order_idrestaurant_idaccepted_atready_at
112025-06-03 12:00:002025-06-03 12:15:00
212025-06-09 12:00:002025-06-09 12:16:00
312025-06-15 20:00:002025-06-15 20:18:00
412025-06-18 13:00:002025-06-18 13:20:00
512025-06-25 13:00:002025-06-25 13:22:00
622025-06-04 09:00:002025-06-04 09:10:00
722025-06-08 09:00:002025-06-08 09:12:00
822025-06-24 09:00:002025-06-24 09:14:00
922025-07-01 09:00:002025-07-01 09:16:00
1032025-06-02 18:00:002025-06-02 18:12:00
1132025-06-10 18:00:002025-06-10 18:14:00
1232025-06-17 18:00:002025-06-17 18:14:00

Output

restaurant_nameweek_start
Biryani Bowl House2025-06-16
Biryani Bowl House2025-06-23

How to solve Swiggy: Kitchens Slowing Down Three Weeks Running

The first step is to give each order its week. WEEKDAY(d) is 0 for Monday through 6 for Sunday, so DATE_SUB(DATE(accepted_at), INTERVAL WEEKDAY(accepted_at) DAY) is the Monday that starts the order's week — a Sunday order belongs to the week that began six days earlier. Grouping by restaurant and that Monday gives one weekly average per restaurant and week. (MySQL's WEEK() and YEARWEEK() depend on a mode argument and are not portable; the Monday date is unambiguous and readable.)

Then compare each week with the two before it. LAG(…, 1) and LAG(…, 2) over the restaurant's weeks bring the previous rows' Mondays and averages onto the current row. The previous row is not necessarily the previous week: if a restaurant had no orders one week, the row before is two weeks back. So the query also checks that the lagged Mondays are exactly 7 and 14 days earlier, and only then requires avg_prep > a1 > a2, strictly. Every week that ends such a triple is reported, so four rising weeks give two rows.

The alternative self-joins the weekly table three times on the exact Mondays, which states the calendar condition directly. Both aggregate the tickets once; the window version then sorts each restaurant's weeks, the join version does three lookups per week.

Reference solution (MySQL)

WITH weekly AS (
  SELECT restaurant_id,
         DATE_SUB(DATE(accepted_at), INTERVAL WEEKDAY(accepted_at) DAY) AS week_start,
         AVG(TIMESTAMPDIFF(MINUTE, accepted_at, ready_at)) AS avg_prep
  FROM KitchenTicket
  GROUP BY restaurant_id, week_start
), lagged AS (
  SELECT restaurant_id, week_start, avg_prep,
         LAG(week_start, 1) OVER (PARTITION BY restaurant_id ORDER BY week_start) AS w1,
         LAG(avg_prep, 1) OVER (PARTITION BY restaurant_id ORDER BY week_start) AS a1,
         LAG(week_start, 2) OVER (PARTITION BY restaurant_id ORDER BY week_start) AS w2,
         LAG(avg_prep, 2) OVER (PARTITION BY restaurant_id ORDER BY week_start) AS a2
  FROM weekly
)
SELECT r.restaurant_name, l.week_start
FROM lagged l
JOIN Restaurant r ON r.restaurant_id = l.restaurant_id
WHERE l.w1 = DATE_SUB(l.week_start, INTERVAL 7 DAY)
  AND l.w2 = DATE_SUB(l.week_start, INTERVAL 14 DAY)
  AND l.avg_prep > l.a1 AND l.a1 > l.a2
ORDER BY r.restaurant_name, l.week_start

Another way

WITH weekly AS (
  SELECT restaurant_id,
         DATE_SUB(DATE(accepted_at), INTERVAL WEEKDAY(accepted_at) DAY) AS week_start,
         AVG(TIMESTAMPDIFF(MINUTE, accepted_at, ready_at)) AS avg_prep
  FROM KitchenTicket
  GROUP BY restaurant_id, week_start
)
SELECT r.restaurant_name, c.week_start
FROM weekly c
JOIN weekly b ON b.restaurant_id = c.restaurant_id AND b.week_start = DATE_SUB(c.week_start, INTERVAL 7 DAY)
JOIN weekly a ON a.restaurant_id = c.restaurant_id AND a.week_start = DATE_SUB(c.week_start, INTERVAL 14 DAY)
JOIN Restaurant r ON r.restaurant_id = c.restaurant_id
WHERE c.avg_prep > b.avg_prep AND b.avg_prep > a.avg_prep
ORDER BY r.restaurant_name, c.week_start

← Swiggy: Units Sold per Item From Instamart Combo Codes · Swiggy: Instamart Items Most Often Bought Together →