Zomato: Orders Placed While the Restaurant Was Closed

Hard
1046.

Zomato: Orders Placed While the Restaurant Was Closed

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

ColumnType
restaurant_idint
weekdayint
opens_minint
closes_minint

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

ColumnType
order_idint
restaurant_idint
ordered_atdatetime

Primary key: order_id.

One row per order, on whole minutes. 2024-10-06 was a Sunday.

Example

The rows must come back in the order shown.

OpeningHours

restaurant_idweekdayopens_mincloses_min
126601380
136601380
146601380
156601380
16660120
17660120

FoodOrder

order_idrestaurant_idordered_at
112024-10-01 10:59:00
212024-10-01 11:00:00
312024-10-01 23:00:00
412024-10-05 01:30:00
512024-10-05 02:00:00
612024-10-06 01:00:00
712024-10-06 13:00:00
822024-10-02 12:00:00

Output

order_idrestaurant_idordered_at
112024-10-01 10:59:00
312024-10-01 23:00:00
512024-10-05 02:00:00
712024-10-06 13:00:00
822024-10-02 12:00:00
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.

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

ColumnType
restaurant_idint
weekdayint
opens_minint
closes_minint

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

ColumnType
order_idint
restaurant_idint
ordered_atdatetime

Primary key: order_id.

One row per order, on whole minutes. 2024-10-06 was a Sunday.

Examples

Example 1

OpeningHours

restaurant_idweekdayopens_mincloses_min
126601380
136601380
146601380
156601380
16660120
17660120

FoodOrder

order_idrestaurant_idordered_at
112024-10-01 10:59:00
212024-10-01 11:00:00
312024-10-01 23:00:00
412024-10-05 01:30:00
512024-10-05 02:00:00
612024-10-06 01:00:00
712024-10-06 13:00:00
822024-10-02 12:00:00

Output

order_idrestaurant_idordered_at
112024-10-01 10:59:00
312024-10-01 23:00:00
512024-10-05 02:00:00
712024-10-06 13:00:00
822024-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_id

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