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
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
Primary key: restaurant_id.
One row per restaurant; names are unique.
Table: KitchenTicket
| Column | Type |
|---|---|
| order_id | int |
| restaurant_id | int |
| accepted_at | datetime |
| ready_at | datetime |
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_id | restaurant_name |
|---|---|
| 1 | Biryani Bowl House |
| 2 | Dosa Corner |
| 3 | Momo Hub |
KitchenTicket
| order_id | restaurant_id | accepted_at | ready_at |
|---|---|---|---|
| 1 | 1 | 2025-06-03 12:00:00 | 2025-06-03 12:15:00 |
| 2 | 1 | 2025-06-09 12:00:00 | 2025-06-09 12:16:00 |
| 3 | 1 | 2025-06-15 20:00:00 | 2025-06-15 20:18:00 |
| 4 | 1 | 2025-06-18 13:00:00 | 2025-06-18 13:20:00 |
| 5 | 1 | 2025-06-25 13:00:00 | 2025-06-25 13:22:00 |
| 6 | 2 | 2025-06-04 09:00:00 | 2025-06-04 09:10:00 |
| 7 | 2 | 2025-06-08 09:00:00 | 2025-06-08 09:12:00 |
| 8 | 2 | 2025-06-24 09:00:00 | 2025-06-24 09:14:00 |
| 9 | 2 | 2025-07-01 09:00:00 | 2025-07-01 09:16:00 |
| 10 | 3 | 2025-06-02 18:00:00 | 2025-06-02 18:12:00 |
| 11 | 3 | 2025-06-10 18:00:00 | 2025-06-10 18:14:00 |
| 12 | 3 | 2025-06-17 18:00:00 | 2025-06-17 18:14:00 |
Output
| restaurant_name | week_start |
|---|---|
| Biryani Bowl House | 2025-06-16 |
| Biryani Bowl House | 2025-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_startAnother 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 →