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
| Column | Type |
|---|---|
| reservation_id | int |
| guest_id | int |
| city | varchar |
| check_in | date |
| check_out | date |
| status | enum(completed, cancelled) |
Primary key: reservation_id.
check_out is always after check_in. city is the listing's city.
Examples
Example 1
Reservation
| reservation_id | guest_id | city | check_in | check_out | status |
|---|---|---|---|---|---|
| 1 | 7 | Lisbon | 2025-04-01 | 2025-04-04 | completed |
| 2 | 7 | Porto | 2025-04-04 | 2025-04-08 | completed |
| 3 | 7 | Madrid | 2025-04-08 | 2025-04-10 | completed |
| 4 | 7 | Seville | 2025-04-20 | 2025-04-22 | completed |
| 5 | 8 | Kyoto | 2025-05-01 | 2025-05-03 | completed |
| 6 | 8 | Osaka | 2025-05-03 | 2025-05-05 | cancelled |
| 7 | 8 | Osaka | 2025-05-04 | 2025-05-06 | completed |
| 8 | 9 | Goa | 2025-01-10 | 2025-01-12 | completed |
| 9 | 9 | Kochi | 2025-01-12 | 2025-01-14 | completed |
Output
| guest_id | trip_start | trip_end | stays | route |
|---|---|---|---|---|
| 7 | 2025-04-01 | 2025-04-10 | 3 | Lisbon > Porto > Madrid |
| 9 | 2025-01-10 | 2025-01-14 | 2 | Goa > 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_startAnother 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 →