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
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
| city | varchar |
| valid_until | date |
| grade | enum(Excellent, Very Good, Good, Needs Improvement) |
| is_active | bool |
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_id | restaurant_name | city | valid_until | grade | is_active |
|---|---|---|---|---|---|
| 1 | Spice Route | Bengaluru | 2024-07-01 | Excellent | 1 |
| 2 | Tandoor Tales | Delhi | 2024-06-30 | Very Good | 1 |
| 3 | Dosa Darbar | Chennai | 2024-07-31 | Good | 1 |
| 4 | Wok Express | Mumbai | 2024-07-15 | Excellent | 0 |
| 5 | Momo Point | Kolkata | 2024-08-01 | Good | 1 |
| 6 | Biryani House | Hyderabad | 2024-07-15 | Needs Improvement | 1 |
| 7 | Kebab Korner | Delhi | 2024-07-15 | Very Good | 1 |
Output
| restaurant_id | restaurant_name | grade | valid_until | days_left |
|---|---|---|---|---|
| 1 | Spice Route | Excellent | 2024-07-01 | 6 |
| 6 | Biryani House | Needs Improvement | 2024-07-15 | 20 |
| 7 | Kebab Korner | Very Good | 2024-07-15 | 20 |
| 3 | Dosa Darbar | Good | 2024-07-31 | 36 |
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_idAnother 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 →