Zomato: Restaurant Partner Anniversaries Falling in November
Zomato's partner team sends every restaurant a thank-you card on the anniversary of the day it joined the platform, with a bigger gift on milestone years.
- Difficulty: Hard
- Topics: Dates, Subqueries, Conditional Logic
- Dialect: MySQL
- Problem: #1044
Problem statement
Zomato's partner team sends every restaurant a thank-you card on the anniversary of the day it joined the platform, with a bigger gift on milestone years. They are preparing November 2024.
Take every active restaurant whose partnership anniversary falls in November 2024 — the day and month of onboarded_on, in 2024 — and that completes at least one whole year on it. Return restaurant_id, restaurant_name, anniversary_date, years_completed (whole years from onboarded_on to the anniversary), tenure_on_nov_15 (whole years from onboarded_on to 2024-11-15 — one less for anniversaries after the 15th), milestone (yes when years_completed is 1, 5 or 10, else no) and orders_in_year — the restaurant's delivered orders dated on or after the date one year before the anniversary and before the anniversary itself. Order by anniversary_date, then restaurant_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: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
| onboarded_on | date |
| is_active | bool |
Primary key: restaurant_id.
One row per restaurant partner; onboarded_on is the day it went live on Zomato.
Table: FoodOrder
| Column | Type |
|---|---|
| order_id | int |
| restaurant_id | int |
| ordered_on | date |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per order placed with a restaurant.
Examples
Example 1
Restaurant
| restaurant_id | restaurant_name | onboarded_on | is_active |
|---|---|---|---|
| 1 | Spice Route | 2019-11-03 | 1 |
| 2 | Momo Point | 2023-11-30 | 1 |
| 3 | Pind Dhaba | 2014-11-15 | 1 |
| 4 | Pasta Street | 2024-11-02 | 1 |
| 5 | Wok Express | 2021-12-01 | 1 |
| 6 | Thali Theatre | 2020-11-20 | 0 |
| 7 | Udupi Corner | 2022-10-31 | 1 |
FoodOrder
| order_id | restaurant_id | ordered_on | status |
|---|---|---|---|
| 1 | 1 | 2023-11-03 | delivered |
| 2 | 1 | 2024-11-02 | delivered |
| 3 | 1 | 2024-11-03 | delivered |
| 4 | 1 | 2024-05-10 | cancelled |
| 5 | 2 | 2024-01-15 | delivered |
| 6 | 2 | 2023-11-29 | delivered |
| 7 | 3 | 2024-06-01 | delivered |
Output
| restaurant_id | restaurant_name | anniversary_date | years_completed | tenure_on_nov_15 | milestone | orders_in_year |
|---|---|---|---|---|---|---|
| 1 | Spice Route | 2024-11-03 | 5 | 5 | yes | 2 |
| 3 | Pind Dhaba | 2024-11-15 | 10 | 10 | yes | 1 |
| 2 | Momo Point | 2024-11-30 | 1 | 0 | yes | 1 |
How to solve Zomato: Restaurant Partner Anniversaries Falling in November
Anniversaries are about the calendar, tenure about whole years. A restaurant's anniversary falls in November 2024 exactly when it joined in a November of an earlier year; a restaurant that joined in November 2024 itself completes 0 years and is excluded by YEAR(onboarded_on) < 2024. The anniversary date is DATE_ADD(onboarded_on, INTERVAL 2024 − YEAR(onboarded_on) YEAR) — every November day exists in every year, so the move never lands on an invalid date (a 29 February joiner would need care; such dates cannot fall in November).
TIMESTAMPDIFF(YEAR, start, end) returns completed years, rounding down: from 2019-11-03 to 2024-11-03 it is 5, to 2024-11-02 only 4. That is why the two tenure columns differ. On the anniversary itself the full count is reached; on the report day, 15 November, restaurants whose anniversary is the 16th or later are still one year short, while one that joined on the 15th has just completed its year. The milestone flag tests the completed years against 1, 5 and 10.
orders_in_year is a correlated count over the half-open window [anniversary − 1 year, anniversary): the previous anniversary is in, the anniversary day itself is not, and cancelled orders are left out. Inactive restaurants get no card and are filtered first.
The alternative builds the anniversary as text — '2024' plus the -MM-DD part of the joining date — computes years as 2024 − YEAR(…), and derives the 15 November tenure by subtracting one when the anniversary's day is after the 15th. Both are single passes over the restaurants; the date functions say the intent more plainly.
Reference solution (MySQL)
WITH anniversaries AS (
SELECT restaurant_id, restaurant_name, onboarded_on,
DATE_ADD(onboarded_on, INTERVAL 2024 - YEAR(onboarded_on) YEAR) AS anniversary_date
FROM Restaurant
WHERE is_active = 1 AND MONTH(onboarded_on) = 11 AND YEAR(onboarded_on) < 2024
)
SELECT a.restaurant_id, a.restaurant_name, a.anniversary_date,
TIMESTAMPDIFF(YEAR, a.onboarded_on, a.anniversary_date) AS years_completed,
TIMESTAMPDIFF(YEAR, a.onboarded_on, '2024-11-15') AS tenure_on_nov_15,
CASE WHEN TIMESTAMPDIFF(YEAR, a.onboarded_on, a.anniversary_date) IN (1, 5, 10) THEN 'yes' ELSE 'no' END AS milestone,
(SELECT COUNT(*) FROM FoodOrder o
WHERE o.restaurant_id = a.restaurant_id AND o.status = 'delivered'
AND o.ordered_on >= DATE_SUB(a.anniversary_date, INTERVAL 1 YEAR)
AND o.ordered_on < a.anniversary_date) AS orders_in_year
FROM anniversaries a
ORDER BY a.anniversary_date, a.restaurant_idAnother way
SELECT restaurant_id, restaurant_name, anniversary_date, years_completed,
years_completed - CASE WHEN DAY(anniversary_date) > 15 THEN 1 ELSE 0 END AS tenure_on_nov_15,
IF(years_completed = 1 OR years_completed = 5 OR years_completed = 10, 'yes', 'no') AS milestone,
orders_in_year
FROM (
SELECT r.restaurant_id, r.restaurant_name,
CAST(CONCAT('2024', SUBSTRING(r.onboarded_on, 5, 6)) AS DATE) AS anniversary_date,
2024 - YEAR(r.onboarded_on) AS years_completed,
(SELECT COUNT(*) FROM FoodOrder o
WHERE o.restaurant_id = r.restaurant_id AND o.status = 'delivered'
AND o.ordered_on >= CAST(CONCAT('2023', SUBSTRING(r.onboarded_on, 5, 6)) AS DATE)
AND o.ordered_on < CAST(CONCAT('2024', SUBSTRING(r.onboarded_on, 5, 6)) AS DATE)) AS orders_in_year
FROM Restaurant r
WHERE r.is_active = 1 AND r.onboarded_on LIKE '%-11-%' AND r.onboarded_on < '2024-01-01'
) t
ORDER BY anniversary_date, restaurant_id← Zomato: Most-Loved Dish of Each Restaurant and Its Complaints · Zomato: Progressive Commission on Each Restaurant's Monthly Sales →