Zomato: Table Reservation No-Show Rate by Meal Slot

Medium
1038.

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.

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

ColumnTypeValues
reservation_idint
restaurant_idint
slot_timedatetime
guestsint
statusenumhonoured, no_show, cancelled

Primary key: reservation_id.

One row per table reservation; slot_time is when the table was booked for.

Example

The rows must come back in the order shown.

TableReservation

reservation_idrestaurant_idslot_timeguestsstatus
112024-07-06 08:30:002honoured
212024-07-06 11:59:004no_show
322024-07-06 12:00:002honoured
422024-07-06 13:30:003honoured
532024-07-07 15:45:002cancelled
632024-07-07 14:00:006no_show
712024-07-06 20:00:002no_show
822024-07-06 21:15:004honoured
932024-07-06 17:30:002cancelled
1022024-07-07 00:30:002honoured

Output

meal_slotbookingsno_showsno_show_rate_pct
breakfast2150
dinner2150
lunch3133.33
late_night100
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: 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

ColumnType
reservation_idint
restaurant_idint
slot_timedatetime
guestsint
statusenum(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_idrestaurant_idslot_timeguestsstatus
112024-07-06 08:30:002honoured
212024-07-06 11:59:004no_show
322024-07-06 12:00:002honoured
422024-07-06 13:30:003honoured
532024-07-07 15:45:002cancelled
632024-07-07 14:00:006no_show
712024-07-06 20:00:002no_show
822024-07-06 21:15:004honoured
932024-07-06 17:30:002cancelled
1022024-07-07 00:30:002honoured

Output

meal_slotbookingsno_showsno_show_rate_pct
breakfast2150
dinner2150
lunch3133.33
late_night100

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_slot

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