Hotel Occupancy on New Year's Eve — SQL Joins Problem
A booking occupies its rooms on every night from check_in up to, but not including, check_out.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #531
Problem statement
A booking occupies its rooms on every night from check_in up to, but not including, check_out. So a booking is in-house on the night of 2024-12-31 when check_in <= '2024-12-31' and check_out > '2024-12-31'. Cancelled bookings occupy nothing.
For every hotel, return hotel_name, rooms_sold (rooms occupied that night) and occupancy_pct = 100 × rooms_sold / total_rooms, rounded to 2 decimals. A hotel with nothing in-house shows 0 and 0. Return the rows in any order.
Tables
Table: Property
| Column | Type |
|---|---|
| hotel_id | int |
| hotel_name | varchar |
| total_rooms | int |
Primary key: hotel_id.
One row per hotel; total_rooms is the sellable inventory.
Table: Reservation
| Column | Type |
|---|---|
| reservation_id | int |
| hotel_id | int |
| check_in | date |
| check_out | date |
| rooms | int |
| status | enum(confirmed, checked_in, cancelled) |
Primary key: reservation_id.
A reservation may hold several rooms. check_out is after check_in.
Examples
Example 1
Property
| hotel_id | hotel_name | total_rooms |
|---|---|---|
| 1 | Seaview Residency | 40 |
| 2 | Pine Ridge Lodge | 12 |
| 3 | Lakeside Haveli | 25 |
Reservation
| reservation_id | hotel_id | check_in | check_out | rooms | status |
|---|---|---|---|---|---|
| 1 | 1 | 2024-12-29 | 2025-01-02 | 6 | checked_in |
| 2 | 1 | 2024-12-31 | 2025-01-01 | 4 | confirmed |
| 3 | 1 | 2024-12-30 | 2024-12-31 | 3 | checked_in |
| 4 | 2 | 2024-12-28 | 2025-01-03 | 5 | checked_in |
| 5 | 2 | 2024-12-31 | 2025-01-02 | 2 | cancelled |
| 6 | 3 | 2025-01-01 | 2025-01-04 | 8 | confirmed |
Output
| hotel_name | rooms_sold | occupancy_pct |
|---|---|---|
| Seaview Residency | 10 | 25 |
| Pine Ridge Lodge | 5 | 41.67 |
| Lakeside Haveli | 0 | 0 |
How to solve Hotel Occupancy on New Year's Eve
A reservation covers a night when the night falls in the half-open range [check_in, check_out): a guest checking in on the 31st sleeps there, a guest checking out on the 31st does not. That is the test check_in <= '2024-12-31' AND check_out > '2024-12-31', together with excluding cancellations.
Every hotel must be reported, so the query starts from Property and LEFT JOINs the reservations. The crucial detail is where the tests go. Put them in WHERE and a hotel with no in-house reservation loses its only (NULL-padded) row and vanishes; put them in the ON clause and the hotel survives with NULLs, which COALESCE(SUM(r.rooms), 0) turns into zero. Occupancy is then 100 * rooms_sold / total_rooms, rounded to two decimals as asked — the divide is decimal division in MySQL, so 7 of 12 rooms is 58.33.
The alternatives compute the same total with a correlated subquery per hotel (writing the night test with BETWEEN and DATE_SUB) or with conditional aggregation over a plain LEFT JOIN. All are one pass over the reservations per hotel; an index on (hotel_id, check_in) keeps it cheap.
Reference solution (MySQL)
SELECT p.hotel_name,
COALESCE(SUM(r.rooms), 0) AS rooms_sold,
ROUND(100 * COALESCE(SUM(r.rooms), 0) / p.total_rooms, 2) AS occupancy_pct
FROM Property p
LEFT JOIN Reservation r
ON r.hotel_id = p.hotel_id
AND r.status <> 'cancelled'
AND r.check_in <= '2024-12-31' AND r.check_out > '2024-12-31'
GROUP BY p.hotel_id, p.hotel_name, p.total_roomsAnother way
SELECT hotel_name, sold AS rooms_sold, ROUND(100 * sold / total_rooms, 2) AS occupancy_pct
FROM (SELECT p.hotel_name, p.total_rooms,
(SELECT COALESCE(SUM(r.rooms), 0) FROM Reservation r
WHERE r.hotel_id = p.hotel_id AND r.status IN ('confirmed', 'checked_in')
AND '2024-12-31' BETWEEN r.check_in AND DATE_SUB(r.check_out, INTERVAL 1 DAY)) AS sold
FROM Property p) tAnother way
SELECT p.hotel_name,
SUM(CASE WHEN r.status <> 'cancelled' AND r.check_in <= '2024-12-31' AND r.check_out > '2024-12-31' THEN r.rooms ELSE 0 END) AS rooms_sold,
ROUND(100 * SUM(CASE WHEN r.status <> 'cancelled' AND r.check_in <= '2024-12-31' AND r.check_out > '2024-12-31' THEN r.rooms ELSE 0 END) / p.total_rooms, 2) AS occupancy_pct
FROM Property p LEFT JOIN Reservation r ON r.hotel_id = p.hotel_id
GROUP BY p.hotel_id, p.hotel_name, p.total_rooms← Loyalty Tier Headcount From Points Balance · Average Daily Rate by Hotel and Month →