Swiggy: Dineout Restaurants With Repeat No-Shows
A Dineout table reservation that the guests never turn up for leaves the restaurant with empty seats on a busy evening.
- Difficulty: Easy
- Topics: Aggregation, Joins
- Dialect: MySQL
- Problem: #954
Problem statement
A Dineout table reservation that the guests never turn up for leaves the restaurant with empty seats on a busy evening. The partnerships team wants the restaurants hit by no-shows more than once.
For every restaurant with at least two reservations marked no_show, return restaurant_name, city, no_show_bookings (how many) and no_show_guests (the total party_size of those reservations). Honoured and cancelled reservations do not count. Order by no_show_guests from highest to lowest, then by restaurant_name.
A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.
Tables
Table: DineoutRestaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
| city | varchar |
Primary key: restaurant_id.
One row per restaurant that takes table reservations. Names are unique.
Table: DineoutBooking
| Column | Type |
|---|---|
| booking_id | int |
| restaurant_id | int |
| slot_date | date |
| party_size | int |
| status | enum(honoured, no_show, cancelled) |
Primary key: booking_id.
One row per table reservation; party_size is the number of guests booked.
Examples
Example 1
DineoutRestaurant
| restaurant_id | restaurant_name | city |
|---|---|---|
| 1 | Tandoor Tales | Delhi |
| 2 | Kerala Kitchen | Kochi |
| 3 | Chaat Junction | Jaipur |
| 4 | Wok Express | Pune |
DineoutBooking
| booking_id | restaurant_id | slot_date | party_size | status |
|---|---|---|---|---|
| 1 | 1 | 2025-05-02 | 4 | no_show |
| 2 | 1 | 2025-05-03 | 2 | honoured |
| 3 | 1 | 2025-05-09 | 6 | no_show |
| 4 | 2 | 2025-05-02 | 3 | no_show |
| 5 | 2 | 2025-05-04 | 2 | cancelled |
| 6 | 3 | 2025-05-10 | 5 | no_show |
| 7 | 3 | 2025-05-11 | 5 | no_show |
| 8 | 4 | 2025-05-10 | 8 | honoured |
Output
| restaurant_name | city | no_show_bookings | no_show_guests |
|---|---|---|---|
| Chaat Junction | Jaipur | 2 | 10 |
| Tandoor Tales | Delhi | 2 | 10 |
How to solve Swiggy: Dineout Restaurants With Repeat No-Shows
Filter, group, then filter the groups. WHERE b.status = 'no_show' keeps only the reservations the question is about, so COUNT(*) per restaurant is the number of no-shows and SUM(party_size) the guests who never came. The condition "at least two" is about a group, not a row, so it goes in HAVING COUNT(*) >= 2, which runs after the grouping; a restaurant with a single no-show forms a group of one and is dropped there.
Grouping by restaurant_id as well as the name and city keeps the select list legal under MySQL's ONLY_FULL_GROUP_BY and would keep two restaurants apart even if they shared a name. The output order is total guests descending, and restaurant_name settles restaurants with the same total.
The alternative aggregates every reservation first, counting no-shows with SUM(status = 'no_show') — a comparison is 1 or 0 in MySQL — and filters the derived table afterwards. Both read the reservations once.
Reference solution (MySQL)
SELECT r.restaurant_name, r.city, COUNT(*) AS no_show_bookings, SUM(b.party_size) AS no_show_guests
FROM DineoutRestaurant r
JOIN DineoutBooking b ON b.restaurant_id = r.restaurant_id
WHERE b.status = 'no_show'
GROUP BY r.restaurant_id, r.restaurant_name, r.city
HAVING COUNT(*) >= 2
ORDER BY no_show_guests DESC, r.restaurant_nameAnother way
SELECT r.restaurant_name, r.city, t.no_show_bookings, t.no_show_guests
FROM DineoutRestaurant r
JOIN (SELECT restaurant_id, SUM(status = 'no_show') AS no_show_bookings,
SUM(CASE WHEN status = 'no_show' THEN party_size ELSE 0 END) AS no_show_guests
FROM DineoutBooking GROUP BY restaurant_id) t ON t.restaurant_id = r.restaurant_id
WHERE t.no_show_bookings >= 2
ORDER BY t.no_show_guests DESC, r.restaurant_name← Swiggy: Packaging Charges Above the Per-Item Cap · Swiggy: Kitchens That Overshot the Promised Prep Time →