Airbnb: Back-to-Back Stays Merged Into Trips

Hard
568.

Airbnb: Back-to-Back Stays Merged Into Trips

Many guests chain stays — three nights in Lisbon, then four in Porto — and the trips team wants to see those journeys as one trip. Two of a guest's completed stays are linked when the later one checks in on the same day the earlier one checks out. A trip is a maximal chain of linked stays.

Return every trip of at least two stays as guest_id, trip_start (first check-in), trip_end (last check-out), stays, and route — the stays' cities in check-in order joined by ' > '. Cancelled reservations are not stays. A guest's completed stays never overlap. Order the rows by guest_id, then trip_start.

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: Reservation

ColumnTypeValues
reservation_idint
guest_idint
cityvarchar
check_indate
check_outdate
statusenumcompleted, cancelled

Primary key: reservation_id.

check_out is always after check_in. city is the listing's city.

Example

The rows must come back in the order shown.

Reservation

reservation_idguest_idcitycheck_incheck_outstatus
17Lisbon2025-04-012025-04-04completed
27Porto2025-04-042025-04-08completed
37Madrid2025-04-082025-04-10completed
47Seville2025-04-202025-04-22completed
58Kyoto2025-05-012025-05-03completed
68Osaka2025-05-032025-05-05cancelled
78Osaka2025-05-042025-05-06completed
89Goa2025-01-102025-01-12completed
99Kochi2025-01-122025-01-14completed

Output

guest_idtrip_starttrip_endstaysroute
72025-04-012025-04-103Lisbon > Porto > Madrid
92025-01-102025-01-142Goa > Kochi
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: Back-to-Back Stays Merged Into Trips — SQL Problem

Many guests chain stays — three nights in Lisbon, then four in Porto — and the trips team wants to see those journeys as one trip.

  • Difficulty: Hard
  • Topics: Window Functions, Strings, Dates
  • Dialect: MySQL
  • Problem: #568

Problem statement

Many guests chain stays — three nights in Lisbon, then four in Porto — and the trips team wants to see those journeys as one trip. Two of a guest's completed stays are linked when the later one checks in on the same day the earlier one checks out. A trip is a maximal chain of linked stays.

Return every trip of at least two stays as guest_id, trip_start (first check-in), trip_end (last check-out), stays, and route — the stays' cities in check-in order joined by ' > '. Cancelled reservations are not stays. A guest's completed stays never overlap. Order the rows by guest_id, then trip_start.

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: Reservation

ColumnType
reservation_idint
guest_idint
cityvarchar
check_indate
check_outdate
statusenum(completed, cancelled)

Primary key: reservation_id.

check_out is always after check_in. city is the listing's city.

Examples

Example 1

Reservation

reservation_idguest_idcitycheck_incheck_outstatus
17Lisbon2025-04-012025-04-04completed
27Porto2025-04-042025-04-08completed
37Madrid2025-04-082025-04-10completed
47Seville2025-04-202025-04-22completed
58Kyoto2025-05-012025-05-03completed
68Osaka2025-05-032025-05-05cancelled
78Osaka2025-05-042025-05-06completed
89Goa2025-01-102025-01-12completed
99Kochi2025-01-122025-01-14completed

Output

guest_idtrip_starttrip_endstaysroute
72025-04-012025-04-103Lisbon > Porto > Madrid
92025-01-102025-01-142Goa > Kochi

How to solve Airbnb: Back-to-Back Stays Merged Into Trips

Trips are islands of stays linked end to start. With the cancelled rows removed, sort each guest's stays by check-in and use LAG(check_out) to see the previous stay: if it checked out on this stay's check-in day the stay continues the trip (flag 0), otherwise — including the guest's first stay, where LAG is NULL — it starts a new one (flag 1). Because completed stays never overlap, the previous stay in check-in order is the only one that could link.

A running SUM of the flags gives each stay its trip number. Grouping by guest and trip number yields the first check-in, the last check-out, the count and, with GROUP_CONCAT(city ORDER BY check_in SEPARATOR ' > '), the route in travel order — the ORDER BY inside the function matters, since group order is otherwise undefined. HAVING COUNT(*) >= 2 drops single stays. In the example, guest 8's cancelled Osaka booking would have linked on 3 May; the real Osaka stay starts on the 4th, so Kyoto stands alone.

The alternative identifies trip starts directly — completed stays that no other completed stay of the guest checks out into — and tags every stay with the latest start at or before it. It needs no window functions but runs correlated subqueries per stay.

Reference solution (MySQL)

WITH stays AS (
  SELECT guest_id, city, check_in, check_out,
         CASE WHEN LAG(check_out) OVER (PARTITION BY guest_id ORDER BY check_in) = check_in THEN 0 ELSE 1 END AS new_trip
  FROM Reservation
  WHERE status = 'completed'
), trips AS (
  SELECT guest_id, city, check_in, check_out,
         SUM(new_trip) OVER (PARTITION BY guest_id ORDER BY check_in ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS trip_no
  FROM stays
)
SELECT guest_id, MIN(check_in) AS trip_start, MAX(check_out) AS trip_end, COUNT(*) AS stays,
       GROUP_CONCAT(city ORDER BY check_in SEPARATOR ' > ') AS route
FROM trips
GROUP BY guest_id, trip_no
HAVING COUNT(*) >= 2
ORDER BY guest_id, trip_start

Another way

WITH done AS (
  SELECT guest_id, city, check_in, check_out FROM Reservation WHERE status = 'completed'
), starts AS (
  SELECT d.guest_id, d.check_in FROM done d
  WHERE NOT EXISTS (SELECT 1 FROM done p WHERE p.guest_id = d.guest_id AND p.check_out = d.check_in)
), tagged AS (
  SELECT d.guest_id, d.city, d.check_in, d.check_out,
         (SELECT MAX(s.check_in) FROM starts s WHERE s.guest_id = d.guest_id AND s.check_in <= d.check_in) AS trip_start
  FROM done d
)
SELECT guest_id, trip_start, MAX(check_out) AS trip_end, COUNT(*) AS stays,
       GROUP_CONCAT(city ORDER BY check_in SEPARATOR ' > ') AS route
FROM tagged
GROUP BY guest_id, trip_start
HAVING COUNT(*) >= 2
ORDER BY guest_id, trip_start

← Airbnb: Collapse Nightly Prices Into Rate Periods · Airbnb: Hosts With Three Cancellations Inside 30 Days →