Airbnb: Collapse Nightly Prices Into Rate Periods

Hard
567.

Airbnb: Collapse Nightly Prices Into Rate Periods

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

ColumnType
listing_idint
nightdate
priceint

Primary key (together): listing_id, night.

The price a host set for one night, in US dollars. Nights the host has not priced have no row.

Example

The rows must come back in the order shown.

NightlyPrice

listing_idnightprice
12025-06-01120
12025-06-02120
12025-06-03150
12025-06-04150
12025-06-05120
12025-06-06120
12025-06-08120
22025-06-0180
22025-06-0280
22025-06-0380

Output

listing_idstart_nightend_nightpricenights
12025-06-012025-06-021202
12025-06-032025-06-041502
12025-06-052025-06-061202
12025-06-082025-06-081201
22025-06-012025-06-03803
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.

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

ColumnType
listing_idint
nightdate
priceint

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_idnightprice
12025-06-01120
12025-06-02120
12025-06-03150
12025-06-04150
12025-06-05120
12025-06-06120
12025-06-08120
22025-06-0180
22025-06-0280
22025-06-0380

Output

listing_idstart_nightend_nightpricenights
12025-06-012025-06-021202
12025-06-032025-06-041502
12025-06-052025-06-061202
12025-06-082025-06-081201
22025-06-012025-06-03803

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_night

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