Zomato: Restaurant Partner Anniversaries Falling in November

Hard
1044.

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. 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

ColumnType
restaurant_idint
restaurant_namevarchar
onboarded_ondate
is_activebool

Primary key: restaurant_id.

One row per restaurant partner; onboarded_on is the day it went live on Zomato.

Table: FoodOrder

ColumnTypeValues
order_idint
restaurant_idint
ordered_ondate
statusenumdelivered, cancelled

Primary key: order_id.

One row per order placed with a restaurant.

Example

The rows must come back in the order shown.

Restaurant

restaurant_idrestaurant_nameonboarded_onis_active
1Spice Route2019-11-031
2Momo Point2023-11-301
3Pind Dhaba2014-11-151
4Pasta Street2024-11-021
5Wok Express2021-12-011
6Thali Theatre2020-11-200
7Udupi Corner2022-10-311

FoodOrder

order_idrestaurant_idordered_onstatus
112023-11-03delivered
212024-11-02delivered
312024-11-03delivered
412024-05-10cancelled
522024-01-15delivered
622023-11-29delivered
732024-06-01delivered

Output

restaurant_idrestaurant_nameanniversary_dateyears_completedtenure_on_nov_15milestoneorders_in_year
1Spice Route2024-11-0355yes2
3Pind Dhaba2024-11-151010yes1
2Momo Point2024-11-3010yes1
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: 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

ColumnType
restaurant_idint
restaurant_namevarchar
onboarded_ondate
is_activebool

Primary key: restaurant_id.

One row per restaurant partner; onboarded_on is the day it went live on Zomato.

Table: FoodOrder

ColumnType
order_idint
restaurant_idint
ordered_ondate
statusenum(delivered, cancelled)

Primary key: order_id.

One row per order placed with a restaurant.

Examples

Example 1

Restaurant

restaurant_idrestaurant_nameonboarded_onis_active
1Spice Route2019-11-031
2Momo Point2023-11-301
3Pind Dhaba2014-11-151
4Pasta Street2024-11-021
5Wok Express2021-12-011
6Thali Theatre2020-11-200
7Udupi Corner2022-10-311

FoodOrder

order_idrestaurant_idordered_onstatus
112023-11-03delivered
212024-11-02delivered
312024-11-03delivered
412024-05-10cancelled
522024-01-15delivered
622023-11-29delivered
732024-06-01delivered

Output

restaurant_idrestaurant_nameanniversary_dateyears_completedtenure_on_nov_15milestoneorders_in_year
1Spice Route2024-11-0355yes2
3Pind Dhaba2024-11-151010yes1
2Momo Point2024-11-3010yes1

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_id

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