Room Nights Sold per Hotel

Easy
524.

Room Nights Sold per Hotel

A room night is one room occupied for one night: a booking from 2024-03-01 to 2024-03-04 is three room nights.

For every hotel with at least one booking that was not cancelled, return hotel_name and room_nights, the total room nights of its non-cancelled bookings. Hotels with only cancelled bookings, or none at all, do not appear. Return the rows in any order.

Tables

Table: Hotel

ColumnType
hotel_idint
hotel_namevarchar
cityvarchar

Primary key: hotel_id.

One row per property.

Table: Booking

ColumnTypeValues
booking_idint
hotel_idint
check_indate
check_outdate
statusenumconfirmed, checked_out, cancelled

Primary key: booking_id.

One room per booking; check_out is always after check_in, and hotel_id always names a hotel.

Example

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

Hotel

hotel_idhotel_namecity
1Seaview ResidencyGoa
2Pink City PalaceJaipur
3Pine Ridge LodgeShimla
4Tea Garden InnMunnar

Booking

booking_idhotel_idcheck_incheck_outstatus
10112024-03-012024-03-04checked_out
10212024-03-022024-03-03confirmed
10322024-02-282024-03-02checked_out
10422024-03-052024-03-09cancelled
10532024-03-102024-03-12cancelled
10612024-03-292024-04-02confirmed

Output

hotel_nameroom_nights
Seaview Residency8
Pink City Palace3
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.

Room Nights Sold per Hotel — SQL Aggregation Problem

A room night is one room occupied for one night: a booking from 2024-03-01 to 2024-03-04 is three room nights.

  • Difficulty: Easy
  • Topics: Aggregation, Dates, Joins
  • Dialect: MySQL
  • Problem: #524

Problem statement

A room night is one room occupied for one night: a booking from 2024-03-01 to 2024-03-04 is three room nights.

For every hotel with at least one booking that was not cancelled, return hotel_name and room_nights, the total room nights of its non-cancelled bookings. Hotels with only cancelled bookings, or none at all, do not appear. Return the rows in any order.

Tables

Table: Hotel

ColumnType
hotel_idint
hotel_namevarchar
cityvarchar

Primary key: hotel_id.

One row per property.

Table: Booking

ColumnType
booking_idint
hotel_idint
check_indate
check_outdate
statusenum(confirmed, checked_out, cancelled)

Primary key: booking_id.

One room per booking; check_out is always after check_in, and hotel_id always names a hotel.

Examples

Example 1

Hotel

hotel_idhotel_namecity
1Seaview ResidencyGoa
2Pink City PalaceJaipur
3Pine Ridge LodgeShimla
4Tea Garden InnMunnar

Booking

booking_idhotel_idcheck_incheck_outstatus
10112024-03-012024-03-04checked_out
10212024-03-022024-03-03confirmed
10322024-02-282024-03-02checked_out
10422024-03-052024-03-09cancelled
10532024-03-102024-03-12cancelled
10612024-03-292024-04-02confirmed

Output

hotel_nameroom_nights
Seaview Residency8
Pink City Palace3

How to solve Room Nights Sold per Hotel

Each booking contributes DATEDIFF(check_out, check_in) room nights: the check-out day itself is not a night, which is exactly what the difference of the two dates counts. The answer per hotel is the sum of that over its bookings, so the query is a join, a filter and a GROUP BY.

The filter status <> 'cancelled' runs before grouping, so a cancelled booking adds nothing. Because the join is an inner join, a hotel whose only bookings were cancelled loses all its rows in WHERE and never forms a group, and a hotel with no bookings never joins at all — both are left out, as asked. Group by hotel_id as well as the name, so two properties sharing a name would still be counted apart.

The alternative sums per hotel_id inside a derived table using TIMESTAMPDIFF(DAY, …), then joins the names on. Either form is one pass over the bookings plus a lookup per hotel.

Reference solution (MySQL)

SELECT h.hotel_name, SUM(DATEDIFF(b.check_out, b.check_in)) AS room_nights
FROM Hotel h
JOIN Booking b ON b.hotel_id = h.hotel_id
WHERE b.status <> 'cancelled'
GROUP BY h.hotel_id, h.hotel_name

Another way

SELECT h.hotel_name, t.room_nights
FROM Hotel h
JOIN (SELECT hotel_id, SUM(TIMESTAMPDIFF(DAY, check_in, check_out)) AS room_nights
      FROM Booking WHERE status IN ('confirmed', 'checked_out') GROUP BY hotel_id) t ON t.hotel_id = h.hotel_id

← Premium New Year Stays in Goa · Flights Departing More Than an Hour Late →