Peak In-House Guests per Hotel — SQL Problem
Housekeeping and the kitchen plan for the busiest night. A stay has its guests in-house on every night from check_in up to, but not including, check_out, so…
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #542
Problem statement
Housekeeping and the kitchen plan for the busiest night. A stay has its guests in-house on every night from check_in up to, but not including, check_out, so a guest who checks out on a day and a guest who checks in that day are never counted together.
For every hotel with at least one stay, return hotel_name, peak_guests (the largest number of guests in-house on any night) and first_peak_night (the earliest night that reached it). Return the rows in any order.
Tables
Table: Hotel
| Column | Type |
|---|---|
| hotel_id | int |
| hotel_name | varchar |
Primary key: hotel_id.
One row per hotel.
Table: GuestStay
| Column | Type |
|---|---|
| stay_id | int |
| hotel_id | int |
| guests | int |
| check_in | date |
| check_out | date |
Primary key: stay_id.
check_out is after check_in; guests is at least 1.
Examples
Example 1
Hotel
| hotel_id | hotel_name |
|---|---|
| 1 | Lakeside Haveli |
| 2 | Pine Ridge Lodge |
| 3 | Tea Garden Inn |
GuestStay
| stay_id | hotel_id | guests | check_in | check_out |
|---|---|---|---|---|
| 1 | 1 | 2 | 2024-10-10 | 2024-10-13 |
| 2 | 1 | 3 | 2024-10-11 | 2024-10-12 |
| 3 | 1 | 4 | 2024-10-13 | 2024-10-15 |
| 4 | 1 | 1 | 2024-10-14 | 2024-10-16 |
| 5 | 2 | 2 | 2024-10-01 | 2024-10-03 |
| 6 | 2 | 2 | 2024-10-03 | 2024-10-04 |
Output
| hotel_name | peak_guests | first_peak_night |
|---|---|---|
| Lakeside Haveli | 5 | 2024-10-11 |
| Pine Ridge Lodge | 2 | 2024-10-01 |
How to solve Peak In-House Guests per Hotel
The occupancy of a hotel is a step function that changes only when someone arrives or leaves, so a sweep line solves it: turn each stay into an arrival event +guests on check_in and a departure event -guests on check_out, and the running total of the events in date order is the number of guests in-house on each night.
Two details make it correct. First, all events of one hotel and day are netted together before the running sum (GROUP BY hotel_id, night), so a departure and an arrival on the same day are applied at once — the departing guest never overlaps the arriving one, matching the half-open [check_in, check_out) rule. That also leaves one row per day, so the running SUM with an explicit ROWS frame has a unique order. Second, the peak and its first night come from ROW_NUMBER() ordered by in_house DESC, night, which picks the earliest of several nights at the same peak.
The alternative uses the fact that the maximum is reached on some check-in date: for every check-in date, sum the guests whose stay covers that night with a correlated subquery, then keep the max and the earliest date reaching it. It is quadratic in the stays per hotel; the sweep is one sort.
Reference solution (MySQL)
WITH moves AS (
SELECT hotel_id, check_in AS night, guests AS delta FROM GuestStay
UNION ALL
SELECT hotel_id, check_out, -guests FROM GuestStay
), daily AS (
SELECT hotel_id, night, SUM(delta) AS delta FROM moves GROUP BY hotel_id, night
), level AS (
SELECT hotel_id, night,
SUM(delta) OVER (PARTITION BY hotel_id ORDER BY night ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS in_house
FROM daily
), ranked AS (
SELECT hotel_id, night, in_house,
ROW_NUMBER() OVER (PARTITION BY hotel_id ORDER BY in_house DESC, night) AS rn
FROM level
)
SELECT h.hotel_name, r.in_house AS peak_guests, r.night AS first_peak_night
FROM ranked r JOIN Hotel h ON h.hotel_id = r.hotel_id
WHERE r.rn = 1Another way
WITH nightly AS (
SELECT s.hotel_id, s.check_in AS night,
(SELECT SUM(t.guests) FROM GuestStay t
WHERE t.hotel_id = s.hotel_id AND t.check_in <= s.check_in AND t.check_out > s.check_in) AS in_house
FROM GuestStay s
), peak AS (
SELECT hotel_id, MAX(in_house) AS peak_guests FROM nightly GROUP BY hotel_id
)
SELECT h.hotel_name, p.peak_guests, MIN(n.night) AS first_peak_night
FROM peak p
JOIN nightly n ON n.hotel_id = p.hotel_id AND n.in_house = p.peak_guests
JOIN Hotel h ON h.hotel_id = p.hotel_id
GROUP BY h.hotel_id, h.hotel_name, p.peak_guests← Flights Delayed Three or More Days in a Row · Repeat-Guest Retention Within 90 Days by Cohort →