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
| Column | Type |
|---|---|
| hotel_id | int |
| hotel_name | varchar |
| city | varchar |
Primary key: hotel_id.
One row per property.
Table: Booking
| Column | Type |
|---|---|
| booking_id | int |
| hotel_id | int |
| check_in | date |
| check_out | date |
| status | enum(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_id | hotel_name | city |
|---|---|---|
| 1 | Seaview Residency | Goa |
| 2 | Pink City Palace | Jaipur |
| 3 | Pine Ridge Lodge | Shimla |
| 4 | Tea Garden Inn | Munnar |
Booking
| booking_id | hotel_id | check_in | check_out | status |
|---|---|---|---|---|
| 101 | 1 | 2024-03-01 | 2024-03-04 | checked_out |
| 102 | 1 | 2024-03-02 | 2024-03-03 | confirmed |
| 103 | 2 | 2024-02-28 | 2024-03-02 | checked_out |
| 104 | 2 | 2024-03-05 | 2024-03-09 | cancelled |
| 105 | 3 | 2024-03-10 | 2024-03-12 | cancelled |
| 106 | 1 | 2024-03-29 | 2024-04-02 | confirmed |
Output
| hotel_name | room_nights |
|---|---|
| Seaview Residency | 8 |
| Pink City Palace | 3 |
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_nameAnother 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 →