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
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
| city | varchar |
Primary key: restaurant_id.
One row per restaurant on Hyperpure.
Table: SupplyOrder
| Column | Type |
|---|---|
| order_id | int |
| restaurant_id | int |
| ordered_on | date |
| amount | int |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per supply order, in rupees.
Examples
Example 1
Restaurant
| restaurant_id | restaurant_name | city |
|---|---|---|
| 1 | Spice Route | Bengaluru |
| 2 | Biryani House | Hyderabad |
| 3 | Dosa Darbar | Chennai |
| 4 | Pind Dhaba | Delhi |
SupplyOrder
| order_id | restaurant_id | ordered_on | amount | status |
|---|---|---|---|---|
| 1 | 1 | 2024-01-05 | 4000 | delivered |
| 2 | 1 | 2024-02-10 | 3500 | delivered |
| 3 | 1 | 2024-03-31 | 2500 | delivered |
| 4 | 1 | 2024-04-01 | 9000 | delivered |
| 5 | 2 | 2024-01-02 | 6000 | delivered |
| 6 | 2 | 2024-01-20 | 2000 | delivered |
| 7 | 2 | 2024-02-14 | 1000 | cancelled |
| 8 | 2 | 2024-03-03 | 5000 | delivered |
| 9 | 3 | 2023-12-31 | 3000 | delivered |
| 10 | 3 | 2024-01-31 | 2000 | delivered |
| 11 | 3 | 2024-02-01 | 3000 | delivered |
| 12 | 3 | 2024-03-01 | 5000 | delivered |
| 13 | 4 | 2024-01-15 | 8000 | delivered |
| 14 | 4 | 2024-02-15 | 8000 | delivered |
| 15 | 4 | 2024-03-15 | 8000 | delivered |
Output
| restaurant_id | restaurant_name | q1_spend |
|---|---|---|
| 4 | Pind Dhaba | 24000 |
| 1 | Spice Route | 10000 |
| 3 | Dosa Darbar | 10000 |
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_idAnother 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 →