Swiggy: Restaurant Offline Minutes Within Opening Hours

Hard
967.

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". 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

ColumnType
restaurant_idint
restaurant_namevarchar
opens_atdatetime
closes_atdatetime

Primary key: restaurant_id.

One row per restaurant, with its declared opening and closing time on the audited day. Names are unique.

Table: StatusLog

ColumnTypeValues
restaurant_idint
changed_atdatetime
statusenumonline, offline

Primary key (together): 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.

Example

The rows must come back in the order shown.

Restaurant

restaurant_idrestaurant_nameopens_atcloses_at
1Biryani Bowl House2025-06-14 11:00:002025-06-14 23:00:00
2Dosa Corner2025-06-14 07:00:002025-06-14 22:00:00
3Momo Hub2025-06-14 12:00:002025-06-14 23:00:00
4Wok Express2025-06-14 11:00:002025-06-14 23:00:00

StatusLog

restaurant_idchanged_atstatus
12025-06-14 10:30:00offline
12025-06-14 11:20:00online
12025-06-14 19:00:00offline
12025-06-14 19:45:00offline
12025-06-14 20:10:00online
12025-06-14 22:40:00offline
22025-06-14 06:00:00offline
22025-06-14 06:50:00online
22025-06-14 21:30:00offline
32025-06-14 12:00:00online
3 more rows

Output

restaurant_nameoffline_minutes
Biryani Bowl House110
Dosa Corner30
Momo Hub30
Wok Express0
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: 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

ColumnType
restaurant_idint
restaurant_namevarchar
opens_atdatetime
closes_atdatetime

Primary key: restaurant_id.

One row per restaurant, with its declared opening and closing time on the audited day. Names are unique.

Table: StatusLog

ColumnType
restaurant_idint
changed_atdatetime
statusenum(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_idrestaurant_nameopens_atcloses_at
1Biryani Bowl House2025-06-14 11:00:002025-06-14 23:00:00
2Dosa Corner2025-06-14 07:00:002025-06-14 22:00:00
3Momo Hub2025-06-14 12:00:002025-06-14 23:00:00
4Wok Express2025-06-14 11:00:002025-06-14 23:00:00

StatusLog

restaurant_idchanged_atstatus
12025-06-14 10:30:00offline
12025-06-14 11:20:00online
12025-06-14 19:00:00offline
12025-06-14 19:45:00offline
12025-06-14 20:10:00online
12025-06-14 22:40:00offline
22025-06-14 06:00:00offline
22025-06-14 06:50:00online
22025-06-14 21:30:00offline
32025-06-14 12:00:00online
32025-06-14 15:00:00offline
32025-06-14 15:30:00online
32025-06-14 23:10:00offline

Output

restaurant_nameoffline_minutes
Biryani Bowl House110
Dosa Corner30
Momo Hub30
Wok Express0

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_name

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