Nightly Rooms Sold Calendar for the Holiday Season
A resort's revenue manager wants a calendar of every night from 2024-12-20 to 2025-01-05 inclusive showing how many rooms were sold for it — including…
- Difficulty: Hard
- Topics: Dates, Joins, Aggregation
- Dialect: MySQL
- Problem: #545
Problem statement
A resort's revenue manager wants a calendar of every night from 2024-12-20 to 2025-01-05 inclusive showing how many rooms were sold for it — including nights where nothing was sold, which must show 0. A reservation occupies its rooms on every night from check_in up to, but not including, check_out; cancelled reservations occupy nothing, and reservations reaching outside the window count only for the nights inside it.
Return night and rooms_sold, one row per night of the window (17 rows), ordered by night.
Tables
Table: ResortReservation
| Column | Type |
|---|---|
| reservation_id | int |
| guest_name | varchar |
| rooms | int |
| check_in | date |
| check_out | date |
| status | enum(confirmed, cancelled) |
Primary key: reservation_id.
One resort. check_out is after check_in.
Examples
Example 1
ResortReservation
| reservation_id | guest_name | rooms | check_in | check_out | status |
|---|---|---|---|---|---|
| 1 | Aditi | 2 | 2024-12-18 | 2024-12-22 | confirmed |
| 2 | Kabir | 1 | 2024-12-21 | 2024-12-23 | confirmed |
| 3 | Riya | 3 | 2024-12-22 | 2024-12-24 | cancelled |
| 4 | Olivia | 4 | 2024-12-30 | 2025-01-02 | confirmed |
| 5 | Farhan | 1 | 2024-12-31 | 2025-01-01 | confirmed |
| 6 | Meera | 2 | 2025-01-04 | 2025-01-09 | confirmed |
Output
| night | rooms_sold |
|---|---|
| 2024-12-20 | 2 |
| 2024-12-21 | 3 |
| 2024-12-22 | 1 |
| 2024-12-23 | 0 |
| 2024-12-24 | 0 |
| 2024-12-25 | 0 |
| 2024-12-26 | 0 |
| 2024-12-27 | 0 |
| 2024-12-28 | 0 |
| 2024-12-29 | 0 |
| 2024-12-30 | 4 |
| 2024-12-31 | 5 |
| 2025-01-01 | 4 |
| 2025-01-02 | 0 |
| 2025-01-03 | 0 |
| 2025-01-04 | 2 |
| 2025-01-05 | 2 |
How to solve Nightly Rooms Sold Calendar for the Holiday Season
The difficulty is the nights that sold nothing: a query over ResortReservation alone can only produce dates that appear in it, so the calendar has to be generated. A recursive CTE does it: the anchor row is 2024-12-20, and the recursive step adds one day with DATE_ADD(night, INTERVAL 1 DAY) while the previous night is before 2025-01-05, which stops after 17 rows.
Each calendar night is then LEFT JOINed to the reservations covering it — confirmed, check_in <= night and check_out > night, the half-open stay rule. All three tests belong in the ON clause: in WHERE they would discard the NULL-padded rows of empty nights. COALESCE(SUM(r.rooms), 0) turns those empty nights into 0. Reservations that start before the window or end after it need no clipping, because they only match the calendar nights they actually cover.
Without recursion, a calendar can be built from a cross join of two digit tables (0–99) filtered to the 17 offsets needed, and the rooms counted with a correlated subquery per night, as in the alternative. Either way the work is one probe of the reservations per night.
Reference solution (MySQL)
WITH RECURSIVE calendar AS (
SELECT CAST('2024-12-20' AS DATE) AS night
UNION ALL
SELECT DATE_ADD(night, INTERVAL 1 DAY) FROM calendar WHERE night < '2025-01-05'
)
SELECT c.night, COALESCE(SUM(r.rooms), 0) AS rooms_sold
FROM calendar c
LEFT JOIN ResortReservation r
ON r.status = 'confirmed' AND r.check_in <= c.night AND r.check_out > c.night
GROUP BY c.night
ORDER BY c.nightAnother way
WITH digits AS (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9),
calendar AS (
SELECT DATE_ADD('2024-12-20', INTERVAL a.d * 10 + b.d DAY) AS night
FROM digits a CROSS JOIN digits b WHERE a.d * 10 + b.d <= 16
)
SELECT c.night,
(SELECT COALESCE(SUM(r.rooms), 0) FROM ResortReservation r
WHERE r.status = 'confirmed' AND c.night BETWEEN r.check_in AND DATE_SUB(r.check_out, INTERVAL 1 DAY)) AS rooms_sold
FROM calendar c
ORDER BY c.night← Median Departure Delay per Airport · App Sessions That End in a Hotel Booking →