Airbnb: Collapse Nightly Prices Into Rate Periods
Hosts set a price per night, and the calendar export stores one row per night.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #567
Problem statement
Hosts set a price per night, and the calendar export stores one row per night. A data engineer at Airbnb needs a compact form for the host dashboard: each run of nights at one price as a single rate period.
A rate period is a maximal run of consecutive nights of one listing with the same price. A night with no row breaks a period, even if the prices on both sides match, and a price that returns after a different one starts a new period. Return every period as listing_id, start_night, end_night, price and nights, ordered by listing_id, then start_night.
A practice scenario set at Airbnb: the tables and data are invented, and this is not a question from, or endorsed by, Airbnb.
Tables
Table: NightlyPrice
| Column | Type |
|---|---|
| listing_id | int |
| night | date |
| price | int |
Primary key: listing_id, night.
The price a host set for one night, in US dollars. Nights the host has not priced have no row.
Examples
Example 1
NightlyPrice
| listing_id | night | price |
|---|---|---|
| 1 | 2025-06-01 | 120 |
| 1 | 2025-06-02 | 120 |
| 1 | 2025-06-03 | 150 |
| 1 | 2025-06-04 | 150 |
| 1 | 2025-06-05 | 120 |
| 1 | 2025-06-06 | 120 |
| 1 | 2025-06-08 | 120 |
| 2 | 2025-06-01 | 80 |
| 2 | 2025-06-02 | 80 |
| 2 | 2025-06-03 | 80 |
Output
| listing_id | start_night | end_night | price | nights |
|---|---|---|---|---|
| 1 | 2025-06-01 | 2025-06-02 | 120 | 2 |
| 1 | 2025-06-03 | 2025-06-04 | 150 | 2 |
| 1 | 2025-06-05 | 2025-06-06 | 120 | 2 |
| 1 | 2025-06-08 | 2025-06-08 | 120 | 1 |
| 2 | 2025-06-01 | 2025-06-03 | 80 | 3 |
How to solve Airbnb: Collapse Nightly Prices Into Rate Periods
A period boundary is a property of two neighbouring rows, so the solution looks at each night beside the one before it. LAG(night) and LAG(price) over the listing's nights in date order give the previous row; the current night continues a period only if that previous night is exactly the day before (DATE_SUB(night, INTERVAL 1 DAY)) and its price is the same. Anything else — a price change, an unpriced gap, or the first row of the listing, where LAG is NULL — starts a period and is flagged 1.
A running SUM of the flags then gives every night the number of its period, and grouping by listing and period number collapses each run into one row with its first night, last night, price and length. The price is constant inside a group; MIN(price) just picks it without a bare column under GROUP BY.
The alternative is the two-row-number trick: number the nights within each listing and price, and subtract from the day number. Consecutive nights at one price share the difference; a gap or an interruption at another price changes it, so 120 before and 120 after a 150 land in different groups. Both are one sort per listing.
Reference solution (MySQL)
WITH marked AS (
SELECT listing_id, night, price,
CASE WHEN LAG(night) OVER w = DATE_SUB(night, INTERVAL 1 DAY)
AND LAG(price) OVER w = price THEN 0 ELSE 1 END AS starts_period
FROM NightlyPrice
WINDOW w AS (PARTITION BY listing_id ORDER BY night)
), numbered AS (
SELECT listing_id, night, price,
SUM(starts_period) OVER (PARTITION BY listing_id ORDER BY night ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS period_no
FROM marked
)
SELECT listing_id, MIN(night) AS start_night, MAX(night) AS end_night, MIN(price) AS price, COUNT(*) AS nights
FROM numbered
GROUP BY listing_id, period_no
ORDER BY listing_id, start_nightAnother way
WITH g AS (
SELECT listing_id, night, price,
DATEDIFF(night, '2000-01-01') - ROW_NUMBER() OVER (PARTITION BY listing_id, price ORDER BY night) AS grp
FROM NightlyPrice
)
SELECT listing_id, MIN(night) AS start_night, MAX(night) AS end_night, price, COUNT(*) AS nights
FROM g
GROUP BY listing_id, price, grp
ORDER BY listing_id, start_night← Airbnb: Longest Open Stretch on Each Listing's Calendar · Airbnb: Back-to-Back Stays Merged Into Trips →