Swiggy: Dineout Restaurants With Repeat No-Shows

Easy
954.

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. 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

ColumnType
restaurant_idint
restaurant_namevarchar
cityvarchar

Primary key: restaurant_id.

One row per restaurant that takes table reservations. Names are unique.

Table: DineoutBooking

ColumnTypeValues
booking_idint
restaurant_idint
slot_datedate
party_sizeint
statusenumhonoured, no_show, cancelled

Primary key: booking_id.

One row per table reservation; party_size is the number of guests booked.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

DineoutRestaurant

restaurant_idrestaurant_namecity
1Tandoor TalesDelhi
2Kerala KitchenKochi
3Chaat JunctionJaipur
4Wok ExpressPune

DineoutBooking

booking_idrestaurant_idslot_dateparty_sizestatus
112025-05-024no_show
212025-05-032honoured
312025-05-096no_show
422025-05-023no_show
522025-05-042cancelled
632025-05-105no_show
732025-05-115no_show
842025-05-108honoured

Output

restaurant_namecityno_show_bookingsno_show_guests
Chaat JunctionJaipur210
Tandoor TalesDelhi210
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.

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

ColumnType
restaurant_idint
restaurant_namevarchar
cityvarchar

Primary key: restaurant_id.

One row per restaurant that takes table reservations. Names are unique.

Table: DineoutBooking

ColumnType
booking_idint
restaurant_idint
slot_datedate
party_sizeint
statusenum(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_idrestaurant_namecity
1Tandoor TalesDelhi
2Kerala KitchenKochi
3Chaat JunctionJaipur
4Wok ExpressPune

DineoutBooking

booking_idrestaurant_idslot_dateparty_sizestatus
112025-05-024no_show
212025-05-032honoured
312025-05-096no_show
422025-05-023no_show
522025-05-042cancelled
632025-05-105no_show
732025-05-115no_show
842025-05-108honoured

Output

restaurant_namecityno_show_bookingsno_show_guests
Chaat JunctionJaipur210
Tandoor TalesDelhi210

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_name

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