Nightly Rooms Sold Calendar for the Holiday Season

Hard
545.

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

ColumnTypeValues
reservation_idint
guest_namevarchar
roomsint
check_indate
check_outdate
statusenumconfirmed, cancelled

Primary key: reservation_id.

One resort. check_out is after check_in.

Example

The rows must come back in the order shown.

ResortReservation

reservation_idguest_nameroomscheck_incheck_outstatus
1Aditi22024-12-182024-12-22confirmed
2Kabir12024-12-212024-12-23confirmed
3Riya32024-12-222024-12-24cancelled
4Olivia42024-12-302025-01-02confirmed
5Farhan12024-12-312025-01-01confirmed
6Meera22025-01-042025-01-09confirmed

Output

nightrooms_sold
2024-12-202
2024-12-213
2024-12-221
2024-12-230
2024-12-240
2024-12-250
2024-12-260
2024-12-270
2024-12-280
2024-12-290
7 more rows
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.

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

ColumnType
reservation_idint
guest_namevarchar
roomsint
check_indate
check_outdate
statusenum(confirmed, cancelled)

Primary key: reservation_id.

One resort. check_out is after check_in.

Examples

Example 1

ResortReservation

reservation_idguest_nameroomscheck_incheck_outstatus
1Aditi22024-12-182024-12-22confirmed
2Kabir12024-12-212024-12-23confirmed
3Riya32024-12-222024-12-24cancelled
4Olivia42024-12-302025-01-02confirmed
5Farhan12024-12-312025-01-01confirmed
6Meera22025-01-042025-01-09confirmed

Output

nightrooms_sold
2024-12-202
2024-12-213
2024-12-221
2024-12-230
2024-12-240
2024-12-250
2024-12-260
2024-12-270
2024-12-280
2024-12-290
2024-12-304
2024-12-315
2025-01-014
2025-01-020
2025-01-030
2025-01-042
2025-01-052

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

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