Swiggy: Restaurant Offline Minutes Within Opening Hours
A restaurant partner can switch itself offline in the partner app when the kitchen is swamped, and customers then see it as "not accepting orders".
- Difficulty: Hard
- Topics: Window Functions, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #967
Problem statement
A restaurant partner can switch itself offline in the partner app when the kitchen is swamped, and customers then see it as "not accepting orders". City ops audits how long each restaurant was offline during the hours it promised to be open.
Each status log row sets the restaurant's status from changed_at until its next log row (or indefinitely if there is none). Before its first row of the day a restaurant is online. A restaurant can log the same status twice in a row. Count, for the audited day, the minutes a restaurant was offline between its opens_at (inclusive) and closes_at (exclusive). Time offline before opening or after closing does not count. Return restaurant_name and offline_minutes for every restaurant (0 when it was never offline in its hours), ordered by offline_minutes descending, then restaurant_name.
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 |
| opens_at | datetime |
| closes_at | datetime |
Primary key: restaurant_id.
One row per restaurant, with its declared opening and closing time on the audited day. Names are unique.
Table: StatusLog
| Column | Type |
|---|---|
| restaurant_id | int |
| changed_at | datetime |
| status | enum(online, offline) |
Primary key: restaurant_id, changed_at.
One row per status the restaurant set in the partner app on the audited day; times are recorded to the minute.
Examples
Example 1
Restaurant
| restaurant_id | restaurant_name | opens_at | closes_at |
|---|---|---|---|
| 1 | Biryani Bowl House | 2025-06-14 11:00:00 | 2025-06-14 23:00:00 |
| 2 | Dosa Corner | 2025-06-14 07:00:00 | 2025-06-14 22:00:00 |
| 3 | Momo Hub | 2025-06-14 12:00:00 | 2025-06-14 23:00:00 |
| 4 | Wok Express | 2025-06-14 11:00:00 | 2025-06-14 23:00:00 |
StatusLog
| restaurant_id | changed_at | status |
|---|---|---|
| 1 | 2025-06-14 10:30:00 | offline |
| 1 | 2025-06-14 11:20:00 | online |
| 1 | 2025-06-14 19:00:00 | offline |
| 1 | 2025-06-14 19:45:00 | offline |
| 1 | 2025-06-14 20:10:00 | online |
| 1 | 2025-06-14 22:40:00 | offline |
| 2 | 2025-06-14 06:00:00 | offline |
| 2 | 2025-06-14 06:50:00 | online |
| 2 | 2025-06-14 21:30:00 | offline |
| 3 | 2025-06-14 12:00:00 | online |
| 3 | 2025-06-14 15:00:00 | offline |
| 3 | 2025-06-14 15:30:00 | online |
| 3 | 2025-06-14 23:10:00 | offline |
Output
| restaurant_name | offline_minutes |
|---|---|
| Biryani Bowl House | 110 |
| Dosa Corner | 30 |
| Momo Hub | 30 |
| Wok Express | 0 |
How to solve Swiggy: Restaurant Offline Minutes Within Opening Hours
A status log records changes, not durations, so the first step converts it into spans. LEAD(changed_at) over each restaurant's rows in time order gives every row the moment its status was replaced; the last row has no successor and gets NULL, which is read as "until closing". A repeated offline row is harmless: it just splits one offline stretch into two adjacent spans that add up to the same minutes.
Next, interval clipping. Only the part of an offline span inside [opens_at, closes_at) counts, so the clipped span starts at GREATEST(span_start, opens_at) and ends at LEAST(span_end, closes_at). An offline stretch that began before opening is cut at opening time; one still running at closing is cut there. A span that lies entirely before opening or after closing clips to an end that is not after its start, and the CASE adds 0 for it instead of a negative number. Online spans are dropped before clipping, and a restaurant whose only log rows are online, or that has none, has nothing to add.
Finally the restaurants are the driving table, LEFT JOINed to the clipped spans, so every restaurant appears and COALESCE turns an empty sum into 0. The alternative finds each span's end with a correlated MIN(changed_at) and clips with CASE instead of GREATEST/LEAST, then sums in a grouped CTE. The window version sorts each restaurant's log once; the correlated one does a lookup per row.
Reference solution (MySQL)
WITH spans AS (
SELECT restaurant_id, status, changed_at AS span_start,
LEAD(changed_at) OVER (PARTITION BY restaurant_id ORDER BY changed_at) AS span_end
FROM StatusLog
), clipped AS (
SELECT s.restaurant_id,
GREATEST(s.span_start, r.opens_at) AS from_at,
LEAST(COALESCE(s.span_end, r.closes_at), r.closes_at) AS to_at
FROM spans s
JOIN Restaurant r ON r.restaurant_id = s.restaurant_id
WHERE s.status = 'offline'
)
SELECT r.restaurant_name,
COALESCE(SUM(CASE WHEN c.to_at > c.from_at THEN TIMESTAMPDIFF(MINUTE, c.from_at, c.to_at) ELSE 0 END), 0) AS offline_minutes
FROM Restaurant r
LEFT JOIN clipped c ON c.restaurant_id = r.restaurant_id
GROUP BY r.restaurant_id, r.restaurant_name
ORDER BY offline_minutes DESC, r.restaurant_nameAnother way
WITH spans AS (
SELECT l.restaurant_id, l.status, l.changed_at AS span_start,
(SELECT MIN(n.changed_at) FROM StatusLog n WHERE n.restaurant_id = l.restaurant_id AND n.changed_at > l.changed_at) AS span_end
FROM StatusLog l
), clipped AS (
SELECT s.restaurant_id,
CASE WHEN s.span_start > r.opens_at THEN s.span_start ELSE r.opens_at END AS from_at,
CASE WHEN s.span_end IS NOT NULL AND s.span_end < r.closes_at THEN s.span_end ELSE r.closes_at END AS to_at
FROM spans s JOIN Restaurant r ON r.restaurant_id = s.restaurant_id
WHERE s.status = 'offline'
), per_restaurant AS (
SELECT restaurant_id, SUM(TIMESTAMPDIFF(MINUTE, from_at, to_at)) AS mins
FROM clipped WHERE to_at > from_at GROUP BY restaurant_id
)
SELECT r.restaurant_name, IFNULL(p.mins, 0) AS offline_minutes
FROM Restaurant r LEFT JOIN per_restaurant p ON p.restaurant_id = r.restaurant_id
ORDER BY offline_minutes DESC, r.restaurant_name← Swiggy: Third Delivered Order of Each New Customer · Swiggy: GMV Report With City Subtotals and a Grand Total →