Peak In-House Guests per Hotel

Hard
542.

Peak In-House Guests per Hotel

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

ColumnType
hotel_idint
hotel_namevarchar

Primary key: hotel_id.

One row per hotel.

Table: GuestStay

ColumnType
stay_idint
hotel_idint
guestsint
check_indate
check_outdate

Primary key: stay_id.

check_out is after check_in; guests is at least 1.

Example

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

Hotel

hotel_idhotel_name
1Lakeside Haveli
2Pine Ridge Lodge
3Tea Garden Inn

GuestStay

stay_idhotel_idguestscheck_incheck_out
1122024-10-102024-10-13
2132024-10-112024-10-12
3142024-10-132024-10-15
4112024-10-142024-10-16
5222024-10-012024-10-03
6222024-10-032024-10-04

Output

hotel_namepeak_guestsfirst_peak_night
Lakeside Haveli52024-10-11
Pine Ridge Lodge22024-10-01
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.

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

ColumnType
hotel_idint
hotel_namevarchar

Primary key: hotel_id.

One row per hotel.

Table: GuestStay

ColumnType
stay_idint
hotel_idint
guestsint
check_indate
check_outdate

Primary key: stay_id.

check_out is after check_in; guests is at least 1.

Examples

Example 1

Hotel

hotel_idhotel_name
1Lakeside Haveli
2Pine Ridge Lodge
3Tea Garden Inn

GuestStay

stay_idhotel_idguestscheck_incheck_out
1122024-10-102024-10-13
2132024-10-112024-10-12
3142024-10-132024-10-15
4112024-10-142024-10-16
5222024-10-012024-10-03
6222024-10-032024-10-04

Output

hotel_namepeak_guestsfirst_peak_night
Lakeside Haveli52024-10-11
Pine Ridge Lodge22024-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 = 1

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