Interview Question Bank · generate_series per booking · HAVING against a joined capacity
7. Overbooked nights
HardHospitalityQuestion #214
Concept: generate_series per booking · HAVING against a joined capacityshow
Capacity questions are per unit of time, so first turn every booking into one row per night it occupies, then count rows per hotel and night and compare with capacity in HAVING. A stay holds a room from check_in through the night before check_out, so the series ends at check_out - 1. Overbooked means strictly more bookings than rooms; a hotel that's exactly full is fine.
Example (different data):
WITH d AS ( SELECT r.kit_id, day::date AS day FROM gear_rentals r CROSS JOIN generate_series(r.out_on, r.back_on - 1, INTERVAL '1 day') AS day WHERE NOT r.cancelled ) SELECT k.name, d.day, COUNT(*) AS rentals, k.units FROM d JOIN kits k ON k.id = d.kit_id GROUP BY k.id, k.name, k.units, d.day HAVING COUNT(*) > k.units;
Your task
Tidewell Hotels' reservations system lets a small hotel take more bookings than it has rooms. Before the summer, the front-office manager wants every night where that has happened, so guests can be moved in time.
A booking holds one room each night from check_in up to the night before check_out. Using every booking that isn't cancelled (stayed or upcoming), find hotel nights with more bookings than the hotel has rooms. Return hotel (name), night, bookings, rooms and rooms_short (bookings - rooms). Sort by night, then hotel.
Starting Postgres in your browser…
Checking your access…