Zomato: Table Reservation No-Show Rate by Meal Slot
Diners can reserve a table through Zomato's dining-out service. Partner restaurants have asked which times of day suffer most from guests who never turn up.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation, Dates
- Dialect: MySQL
- Problem: #1038
Problem statement
Diners can reserve a table through Zomato's dining-out service. Partner restaurants have asked which times of day suffer most from guests who never turn up.
Put each reservation in a meal slot by the hour of slot_time: 7–11 breakfast, 12–15 lunch, 16–18 evening, 19–23 dinner, and 0–6 late_night. Cancelled reservations are left out entirely. For each slot with at least one remaining reservation, return meal_slot, bookings (honoured + no-show), no_shows and no_show_rate_pct = 100 × no_shows / bookings rounded to 2 decimals. Order by no_show_rate_pct descending, then meal_slot 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: TableReservation
| Column | Type |
|---|---|
| reservation_id | int |
| restaurant_id | int |
| slot_time | datetime |
| guests | int |
| status | enum(honoured, no_show, cancelled) |
Primary key: reservation_id.
One row per table reservation; slot_time is when the table was booked for.
Examples
Example 1
TableReservation
| reservation_id | restaurant_id | slot_time | guests | status |
|---|---|---|---|---|
| 1 | 1 | 2024-07-06 08:30:00 | 2 | honoured |
| 2 | 1 | 2024-07-06 11:59:00 | 4 | no_show |
| 3 | 2 | 2024-07-06 12:00:00 | 2 | honoured |
| 4 | 2 | 2024-07-06 13:30:00 | 3 | honoured |
| 5 | 3 | 2024-07-07 15:45:00 | 2 | cancelled |
| 6 | 3 | 2024-07-07 14:00:00 | 6 | no_show |
| 7 | 1 | 2024-07-06 20:00:00 | 2 | no_show |
| 8 | 2 | 2024-07-06 21:15:00 | 4 | honoured |
| 9 | 3 | 2024-07-06 17:30:00 | 2 | cancelled |
| 10 | 2 | 2024-07-07 00:30:00 | 2 | honoured |
Output
| meal_slot | bookings | no_shows | no_show_rate_pct |
|---|---|---|---|
| breakfast | 2 | 1 | 50 |
| dinner | 2 | 1 | 50 |
| lunch | 3 | 1 | 33.33 |
| late_night | 1 | 0 | 0 |
How to solve Zomato: Table Reservation No-Show Rate by Meal Slot
The slot is a label derived from the hour, so build it first: HOUR(slot_time) returns 0–23, and a CASE with ranges maps it to the five names. Each WHEN is tested in order and the ELSE catches hours 0–6. Note the boundaries — 11:59 is still breakfast (hour 11) and 12:00 is lunch.
Cancelled reservations are dropped in the CTE's WHERE, so they are counted nowhere: the rate is no-shows out of the reservations that were expected to arrive. A slot whose reservations were all cancelled has no rows left and does not appear.
Grouping by the derived meal_slot then gives COUNT(*) bookings and a conditional SUM of no-shows; their ratio × 100, rounded, is the rate. Ordering by the rounded rate with the slot name as the tie-breaker makes equal rates come out alphabetically.
The alternative nests IF calls with upper bounds instead of ranges, and computes the rate as AVG of a 0/1 flag — the average of a flag is the share of rows where it is 1. Either way it is one scan of the table.
Reference solution (MySQL)
WITH slotted AS (
SELECT status,
CASE WHEN HOUR(slot_time) BETWEEN 7 AND 11 THEN 'breakfast'
WHEN HOUR(slot_time) BETWEEN 12 AND 15 THEN 'lunch'
WHEN HOUR(slot_time) BETWEEN 16 AND 18 THEN 'evening'
WHEN HOUR(slot_time) BETWEEN 19 AND 23 THEN 'dinner'
ELSE 'late_night' END AS meal_slot
FROM TableReservation
WHERE status <> 'cancelled'
)
SELECT meal_slot, COUNT(*) AS bookings,
SUM(CASE WHEN status = 'no_show' THEN 1 ELSE 0 END) AS no_shows,
ROUND(100 * SUM(CASE WHEN status = 'no_show' THEN 1 ELSE 0 END) / COUNT(*), 2) AS no_show_rate_pct
FROM slotted
GROUP BY meal_slot
ORDER BY no_show_rate_pct DESC, meal_slotAnother way
SELECT meal_slot, COUNT(*) AS bookings, SUM(is_no_show) AS no_shows,
ROUND(AVG(is_no_show) * 100, 2) AS no_show_rate_pct
FROM (
SELECT IF(HOUR(slot_time) < 7, 'late_night',
IF(HOUR(slot_time) < 12, 'breakfast',
IF(HOUR(slot_time) < 16, 'lunch',
IF(HOUR(slot_time) < 19, 'evening', 'dinner')))) AS meal_slot,
IF(status = 'no_show', 1, 0) AS is_no_show
FROM TableReservation
WHERE status IN ('honoured', 'no_show')
) t
GROUP BY meal_slot
ORDER BY no_show_rate_pct DESC, meal_slot ASC← Zomato: Restaurants Buying From Hyperpure Every Month of Q1 · Zomato: Top Intercity Legends Dish in Each Destination City →