Zomato: Hygiene Certificates Expiring in July

Easy
1023.

Zomato: Hygiene Certificates Expiring in July

EasyBasicsDates

MySQL

Zomato's restaurant-compliance team shows a hygiene grade on every listed restaurant, and a grade is only shown while its certificate is valid. On 2024-06-25 they want to book re-audits for every certificate that lapses next month.

Return each active restaurant (is_active = 1) whose valid_until falls between 2024-07-01 and 2024-07-31 inclusive, with the columns restaurant_id, restaurant_name, grade, valid_until and days_left — the number of days from 2024-06-25 to valid_until. Order the rows by valid_until, then by 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: HygieneCertificate

ColumnTypeValues
restaurant_idint
restaurant_namevarchar
cityvarchar
valid_untildate
gradeenumExcellent, Very Good, Good, Needs Improvement
is_activebool

Primary key: restaurant_id.

One row per restaurant: its current hygiene certificate. is_active is 0 for a restaurant delisted from the app.

Example

The rows must come back in the order shown.

HygieneCertificate

restaurant_idrestaurant_namecityvalid_untilgradeis_active
1Spice RouteBengaluru2024-07-01Excellent1
2Tandoor TalesDelhi2024-06-30Very Good1
3Dosa DarbarChennai2024-07-31Good1
4Wok ExpressMumbai2024-07-15Excellent0
5Momo PointKolkata2024-08-01Good1
6Biryani HouseHyderabad2024-07-15Needs Improvement1
7Kebab KornerDelhi2024-07-15Very Good1

Output

restaurant_idrestaurant_namegradevalid_untildays_left
1Spice RouteExcellent2024-07-016
6Biryani HouseNeeds Improvement2024-07-1520
7Kebab KornerVery Good2024-07-1520
3Dosa DarbarGood2024-07-3136
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: Hygiene Certificates Expiring in July — SQL Problem

Zomato's restaurant-compliance team shows a hygiene grade on every listed restaurant, and a grade is only shown while its certificate is valid.

  • Difficulty: Easy
  • Topics: Basics, Dates
  • Dialect: MySQL
  • Problem: #1023

Problem statement

Zomato's restaurant-compliance team shows a hygiene grade on every listed restaurant, and a grade is only shown while its certificate is valid. On 2024-06-25 they want to book re-audits for every certificate that lapses next month.

Return each active restaurant (is_active = 1) whose valid_until falls between 2024-07-01 and 2024-07-31 inclusive, with the columns restaurant_id, restaurant_name, grade, valid_until and days_left — the number of days from 2024-06-25 to valid_until. Order the rows by valid_until, then by 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: HygieneCertificate

ColumnType
restaurant_idint
restaurant_namevarchar
cityvarchar
valid_untildate
gradeenum(Excellent, Very Good, Good, Needs Improvement)
is_activebool

Primary key: restaurant_id.

One row per restaurant: its current hygiene certificate. is_active is 0 for a restaurant delisted from the app.

Examples

Example 1

HygieneCertificate

restaurant_idrestaurant_namecityvalid_untilgradeis_active
1Spice RouteBengaluru2024-07-01Excellent1
2Tandoor TalesDelhi2024-06-30Very Good1
3Dosa DarbarChennai2024-07-31Good1
4Wok ExpressMumbai2024-07-15Excellent0
5Momo PointKolkata2024-08-01Good1
6Biryani HouseHyderabad2024-07-15Needs Improvement1
7Kebab KornerDelhi2024-07-15Very Good1

Output

restaurant_idrestaurant_namegradevalid_untildays_left
1Spice RouteExcellent2024-07-016
6Biryani HouseNeeds Improvement2024-07-1520
7Kebab KornerVery Good2024-07-1520
3Dosa DarbarGood2024-07-3136

How to solve Zomato: Hygiene Certificates Expiring in July

Every certificate is one row, so this is a filter plus one computed column. The WHERE clause keeps active restaurants (is_active = 1) whose valid_until lies in July 2024; BETWEEN '2024-07-01' AND '2024-07-31' is inclusive at both ends, so certificates lapsing on the 1st or the 31st are kept, while 30 June and 1 August are not.

days_left is DATEDIFF(valid_until, '2024-06-25') — the later date first, so the count is positive. Two certificates often expire on the same day, which is why the order needs restaurant_id as a tie-breaker after valid_until.

The alternative tests the month with YEAR() and MONTH() and measures the gap with TIMESTAMPDIFF(DAY, …). That reads well but cannot use an index on valid_until; the BETWEEN range can, so on a large table it is the better habit. Either way the query is one pass over the rows.

Reference solution (MySQL)

SELECT restaurant_id, restaurant_name, grade, valid_until,
       DATEDIFF(valid_until, '2024-06-25') AS days_left
FROM HygieneCertificate
WHERE is_active = 1
  AND valid_until BETWEEN '2024-07-01' AND '2024-07-31'
ORDER BY valid_until, restaurant_id

Another way

SELECT restaurant_id, restaurant_name, grade, valid_until,
       TIMESTAMPDIFF(DAY, '2024-06-25', valid_until) AS days_left
FROM HygieneCertificate
WHERE is_active = 1 AND YEAR(valid_until) = 2024 AND MONTH(valid_until) = 7
ORDER BY valid_until ASC, restaurant_id ASC

← Walmart: Assigning Pallets to Trailers Under a Weight Limit · Zomato: Veg, Non-Veg and Egg Dishes on Every Menu →