Hotel Occupancy on New Year's Eve

Medium
531.

Hotel Occupancy on New Year's Eve

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

ColumnType
hotel_idint
hotel_namevarchar
total_roomsint

Primary key: hotel_id.

One row per hotel; total_rooms is the sellable inventory.

Table: Reservation

ColumnTypeValues
reservation_idint
hotel_idint
check_indate
check_outdate
roomsint
statusenumconfirmed, checked_in, cancelled

Primary key: reservation_id.

A reservation may hold several rooms. check_out is after check_in.

Example

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

Property

hotel_idhotel_nametotal_rooms
1Seaview Residency40
2Pine Ridge Lodge12
3Lakeside Haveli25

Reservation

reservation_idhotel_idcheck_incheck_outroomsstatus
112024-12-292025-01-026checked_in
212024-12-312025-01-014confirmed
312024-12-302024-12-313checked_in
422024-12-282025-01-035checked_in
522024-12-312025-01-022cancelled
632025-01-012025-01-048confirmed

Output

hotel_namerooms_soldoccupancy_pct
Seaview Residency1025
Pine Ridge Lodge541.67
Lakeside Haveli00
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.

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

ColumnType
hotel_idint
hotel_namevarchar
total_roomsint

Primary key: hotel_id.

One row per hotel; total_rooms is the sellable inventory.

Table: Reservation

ColumnType
reservation_idint
hotel_idint
check_indate
check_outdate
roomsint
statusenum(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_idhotel_nametotal_rooms
1Seaview Residency40
2Pine Ridge Lodge12
3Lakeside Haveli25

Reservation

reservation_idhotel_idcheck_incheck_outroomsstatus
112024-12-292025-01-026checked_in
212024-12-312025-01-014confirmed
312024-12-302024-12-313checked_in
422024-12-282025-01-035checked_in
522024-12-312025-01-022cancelled
632025-01-012025-01-048confirmed

Output

hotel_namerooms_soldoccupancy_pct
Seaview Residency1025
Pine Ridge Lodge541.67
Lakeside Haveli00

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_rooms

Another 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) t

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