Zomato: Orders Placed While the Restaurant Was Closed
A restaurant's listing should accept orders only during its published opening hours.
- Difficulty: Hard
- Topics: Dates, Subqueries, Conditional Logic
- Dialect: MySQL
- Problem: #1046
Problem statement
A restaurant's listing should accept orders only during its published opening hours. After a bug report, the listings team wants every order that slipped through while the restaurant was closed.
OpeningHours has at most one row per restaurant and weekday (weekday uses DAYOFWEEK numbering, 1 = Sunday … 7 = Saturday; no row means closed all day). Hours are minutes after midnight. When closes_min > opens_min the restaurant is open from opens_min up to but not including closes_min that day; when closes_min < opens_min it stays open past midnight, until closes_min on the next day (Saturday's late hours run into Sunday). An order is placed while open if its time falls in its own day's hours or in the previous day's late hours. Return order_id, restaurant_id and ordered_at of the orders placed while closed, ordered by order_id.
A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.
Tables
Table: OpeningHours
| Column | Type |
|---|---|
| restaurant_id | int |
| weekday | int |
| opens_min | int |
| closes_min | int |
Primary key: restaurant_id, weekday.
One row per restaurant per open weekday; 660 is 11:00 and 120 is 02:00. closes_min never equals opens_min.
Table: FoodOrder
| Column | Type |
|---|---|
| order_id | int |
| restaurant_id | int |
| ordered_at | datetime |
Primary key: order_id.
One row per order, on whole minutes. 2024-10-06 was a Sunday.
Examples
Example 1
OpeningHours
| restaurant_id | weekday | opens_min | closes_min |
|---|---|---|---|
| 1 | 2 | 660 | 1380 |
| 1 | 3 | 660 | 1380 |
| 1 | 4 | 660 | 1380 |
| 1 | 5 | 660 | 1380 |
| 1 | 6 | 660 | 120 |
| 1 | 7 | 660 | 120 |
FoodOrder
| order_id | restaurant_id | ordered_at |
|---|---|---|
| 1 | 1 | 2024-10-01 10:59:00 |
| 2 | 1 | 2024-10-01 11:00:00 |
| 3 | 1 | 2024-10-01 23:00:00 |
| 4 | 1 | 2024-10-05 01:30:00 |
| 5 | 1 | 2024-10-05 02:00:00 |
| 6 | 1 | 2024-10-06 01:00:00 |
| 7 | 1 | 2024-10-06 13:00:00 |
| 8 | 2 | 2024-10-02 12:00:00 |
Output
| order_id | restaurant_id | ordered_at |
|---|---|---|
| 1 | 1 | 2024-10-01 10:59:00 |
| 3 | 1 | 2024-10-01 23:00:00 |
| 5 | 1 | 2024-10-05 02:00:00 |
| 7 | 1 | 2024-10-06 13:00:00 |
| 8 | 2 | 2024-10-02 12:00:00 |
How to solve Zomato: Orders Placed While the Restaurant Was Closed
The difficulty is that a day's hours do not stay inside the day. A restaurant open 11:00–02:00 on Friday is open at 01:30 on Saturday, so an order must be checked against two rows: its own weekday's, and the previous weekday's when that one runs past midnight.
Against its own day, the order's minute-of-day m = HOUR*60 + MINUTE must be at least opens_min, and either before closes_min (normal hours) or anywhere until midnight (late hours, closes_min < opens_min). Against the previous day, the row must be a late one and m < closes_min. The previous weekday comes from DAYOFWEEK(DATE_SUB(ordered_at, INTERVAL 1 DAY)), which wraps Sunday back to Saturday without any arithmetic. An order is open if either test finds a row, so a closed order is one where both NOT EXISTS hold; a restaurant with no hours at all fails both and every order of it is reported. Closing times are exclusive: an order at exactly 23:00 for hours ending at 23:00 is closed.
The alternative removes the day boundary altogether. Each row becomes a span on a 10,080-minute week ((weekday − 1) × 1440 + opens), whose length is (closes − opens + 1440) MOD 1440, so late hours simply extend past the next midnight. The order becomes a week-minute too, and it is open when some span contains it — or, for Saturday-night spans that run past the end of the week, contains it shifted by one week (week_min + 10080). A LEFT JOIN … IS NULL keeps the orders no span contains. It is the standard trick for cyclic time: unroll the cycle once.
Reference solution (MySQL)
SELECT o.order_id, o.restaurant_id, o.ordered_at
FROM FoodOrder o
WHERE NOT EXISTS (
SELECT 1 FROM OpeningHours h
WHERE h.restaurant_id = o.restaurant_id
AND h.weekday = DAYOFWEEK(o.ordered_at)
AND HOUR(o.ordered_at) * 60 + MINUTE(o.ordered_at) >= h.opens_min
AND (h.closes_min < h.opens_min OR HOUR(o.ordered_at) * 60 + MINUTE(o.ordered_at) < h.closes_min)
)
AND NOT EXISTS (
SELECT 1 FROM OpeningHours h
WHERE h.restaurant_id = o.restaurant_id
AND h.weekday = DAYOFWEEK(DATE_SUB(o.ordered_at, INTERVAL 1 DAY))
AND h.closes_min < h.opens_min
AND HOUR(o.ordered_at) * 60 + MINUTE(o.ordered_at) < h.closes_min
)
ORDER BY o.order_idAnother way
WITH week_spans AS (
SELECT restaurant_id,
(weekday - 1) * 1440 + opens_min AS span_start,
(weekday - 1) * 1440 + opens_min + MOD(closes_min - opens_min + 1440, 1440) AS span_end
FROM OpeningHours
),
placed AS (
SELECT order_id, restaurant_id, ordered_at,
(DAYOFWEEK(ordered_at) - 1) * 1440 + HOUR(ordered_at) * 60 + MINUTE(ordered_at) AS week_min
FROM FoodOrder
)
SELECT p.order_id, p.restaurant_id, p.ordered_at
FROM placed p
LEFT JOIN week_spans s
ON s.restaurant_id = p.restaurant_id
AND ((p.week_min >= s.span_start AND p.week_min < s.span_end)
OR p.week_min + 10080 < s.span_end)
WHERE s.restaurant_id IS NULL
ORDER BY p.order_id← Zomato: Progressive Commission on Each Restaurant's Monthly Sales · Zomato: Ratings Before and After a Hygiene Audit →