Zomato: Restaurants Buying From Hyperpure Every Month of Q1

Medium
1037.

Zomato: Restaurants Buying From Hyperpure Every Month of Q1

Hyperpure's account managers call a restaurant a regular when it received supplies in every month of a quarter. They want the regulars of the first quarter of 2024.

Return each restaurant with at least one delivered order in each of January, February and March 2024, with restaurant_id, restaurant_name and q1_spend — the total amount of its delivered orders dated 2024-01-01 to 2024-03-31. Cancelled orders do not count for anything. Order by q1_spend descending, then restaurant_id ascending.

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: Restaurant

ColumnType
restaurant_idint
restaurant_namevarchar
cityvarchar

Primary key: restaurant_id.

One row per restaurant on Hyperpure.

Table: SupplyOrder

ColumnTypeValues
order_idint
restaurant_idint
ordered_ondate
amountint
statusenumdelivered, cancelled

Primary key: order_id.

One row per supply order, in rupees.

Example

The rows must come back in the order shown.

Restaurant

restaurant_idrestaurant_namecity
1Spice RouteBengaluru
2Biryani HouseHyderabad
3Dosa DarbarChennai
4Pind DhabaDelhi

SupplyOrder

order_idrestaurant_idordered_onamountstatus
112024-01-054000delivered
212024-02-103500delivered
312024-03-312500delivered
412024-04-019000delivered
522024-01-026000delivered
622024-01-202000delivered
722024-02-141000cancelled
822024-03-035000delivered
932023-12-313000delivered
1032024-01-312000delivered
5 more rows

Output

restaurant_idrestaurant_nameq1_spend
4Pind Dhaba24000
1Spice Route10000
3Dosa Darbar10000
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: Restaurants Buying From Hyperpure Every Month of Q1

Hyperpure's account managers call a restaurant a regular when it received supplies in every month of a quarter.

  • Difficulty: Medium
  • Topics: Aggregation, Dates, Joins
  • Dialect: MySQL
  • Problem: #1037

Problem statement

Hyperpure's account managers call a restaurant a regular when it received supplies in every month of a quarter. They want the regulars of the first quarter of 2024.

Return each restaurant with at least one delivered order in each of January, February and March 2024, with restaurant_id, restaurant_name and q1_spend — the total amount of its delivered orders dated 2024-01-01 to 2024-03-31. Cancelled orders do not count for anything. Order by q1_spend descending, then restaurant_id ascending.

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: Restaurant

ColumnType
restaurant_idint
restaurant_namevarchar
cityvarchar

Primary key: restaurant_id.

One row per restaurant on Hyperpure.

Table: SupplyOrder

ColumnType
order_idint
restaurant_idint
ordered_ondate
amountint
statusenum(delivered, cancelled)

Primary key: order_id.

One row per supply order, in rupees.

Examples

Example 1

Restaurant

restaurant_idrestaurant_namecity
1Spice RouteBengaluru
2Biryani HouseHyderabad
3Dosa DarbarChennai
4Pind DhabaDelhi

SupplyOrder

order_idrestaurant_idordered_onamountstatus
112024-01-054000delivered
212024-02-103500delivered
312024-03-312500delivered
412024-04-019000delivered
522024-01-026000delivered
622024-01-202000delivered
722024-02-141000cancelled
822024-03-035000delivered
932023-12-313000delivered
1032024-01-312000delivered
1132024-02-013000delivered
1232024-03-015000delivered
1342024-01-158000delivered
1442024-02-158000delivered
1542024-03-158000delivered

Output

restaurant_idrestaurant_nameq1_spend
4Pind Dhaba24000
1Spice Route10000
3Dosa Darbar10000

How to solve Zomato: Restaurants Buying From Hyperpure Every Month of Q1

This is a small case of relational division: keep the restaurants that have a row for each of a fixed set of months. With the rows narrowed by WHERE to delivered orders between 1 January and 31 March, every remaining month number is 1, 2 or 3, so a restaurant covered all three exactly when COUNT(DISTINCT MONTH(ordered_on)) = 3.

The DISTINCT is essential — a restaurant with two January orders and one March order has three rows but only two months. The date filter must come before grouping, too: an order on 31 December or 1 April would otherwise add a fourth month or inflate the spend. Cancelled orders are dropped in the same WHERE, so a cancelled February order cannot stand in for a missing delivery.

The same filtered rows give SUM(amount), the Q1 spend, and the order uses restaurant_id to break ties in spend.

The alternative spells the division out with three EXISTS tests, one per month, and computes the spend with a scalar subquery. It is explicit but scales poorly as the set of months grows; the counting version only needs its constant changed.

Reference solution (MySQL)

SELECT r.restaurant_id, r.restaurant_name, SUM(o.amount) AS q1_spend
FROM Restaurant r
JOIN SupplyOrder o ON o.restaurant_id = r.restaurant_id
WHERE o.status = 'delivered'
  AND o.ordered_on BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY r.restaurant_id, r.restaurant_name
HAVING COUNT(DISTINCT MONTH(o.ordered_on)) = 3
ORDER BY q1_spend DESC, r.restaurant_id

Another way

SELECT r.restaurant_id, r.restaurant_name,
       (SELECT SUM(amount) FROM SupplyOrder o WHERE o.restaurant_id = r.restaurant_id AND o.status = 'delivered'
          AND o.ordered_on >= '2024-01-01' AND o.ordered_on <= '2024-03-31') AS q1_spend
FROM Restaurant r
WHERE EXISTS (SELECT 1 FROM SupplyOrder o WHERE o.restaurant_id = r.restaurant_id AND o.status = 'delivered' AND o.ordered_on BETWEEN '2024-01-01' AND '2024-01-31')
  AND EXISTS (SELECT 1 FROM SupplyOrder o WHERE o.restaurant_id = r.restaurant_id AND o.status = 'delivered' AND o.ordered_on BETWEEN '2024-02-01' AND '2024-02-29')
  AND EXISTS (SELECT 1 FROM SupplyOrder o WHERE o.restaurant_id = r.restaurant_id AND o.status = 'delivered' AND o.ordered_on BETWEEN '2024-03-01' AND '2024-03-31')
ORDER BY q1_spend DESC, r.restaurant_id ASC

← Zomato: District Event Sell-Through by Category · Zomato: Table Reservation No-Show Rate by Meal Slot →