Airbnb mid Self JOINCOALESCEJOINDate ComparisonORDER BY

Find every pair of reservations for the same listing whose stay dates genuinely overlap — not reservations that simply share a listing, and not back-to-back turnovers where one checkout date equals the next check-in date. Treat a NULL check_out as an open-ended stay that hasn't ended yet (it overlaps with any later reservation on that listing). Return each overlapping pair once (not twice, and never a reservation paired with itself) as listing_id, guest_1, check_in_1, check_out_1, guest_2, check_in_2, check_out_2, sorted by listing_id then guest_1.

Airbnb · Overlapping Bookings — practice this real-world SQL scenario live in your browser.

📖 Story Airbnb · Overlapping Bookings

Tuesday afternoon at Airbnb's Trust & Operations team. A sync bug between two calendar providers let a handful of listings get booked twice for overlapping dates before the fix shipped. The reservations table has one row per booking, and a few long-term stays were entered with no checkout date at all — the guest hadn't set one yet, meaning the stay was still open-ended, not a data-entry mistake. Before support can reach out to double-booked guests, every pair of reservations on the same listing whose stay dates actually overlap needs to be found — same-day turnovers, where one guest checks out exactly as another checks in, are normal and shouldn't be flagged.

🎯 Your Mission

📋 Table Structure

🗂 reservations

reservation_id INTEGER 1

listing_id INTEGER 100

guest_name TEXT Aarav

check_in TEXT 2026-08-01

check_out TEXT 2026-08-05

⚡ Step-by-Step Walkthrough

Look at the raw reservations, grouped by listing

query.sql

SELECT *
FROM reservations
ORDER BY listing_id, check_in;

💡 Explanation

reservations has one row per booking, not one row per listing — listing_id repeats whenever the same property has more than one reservation; here 100 and 300 each have more than one.
Divya's reservation on listing 300 has NULL check_out — she's still staying, with no checkout date recorded yet, which is different from a data gap; it means 'not ended,' not 'unknown.'
Listing 100 has three reservations back to back: Aarav ends 08-05 exactly when Karthik begins, which is a normal same-day turnover, not a double-booking — that boundary case is exactly what the final query needs to get right.
Nothing is compared yet — this step only confirms which listings even have more than one reservation to check.

Self-join reservations to their own table to build every same-listing pair

query.sql

SELECT r1.listing_id,
       r1.guest_name AS guest_1,
       r2.guest_name AS guest_2
FROM reservations r1
JOIN reservations r2
  ON r1.listing_id = r2.listing_id
 AND r1.reservation_id < r2.reservation_id
ORDER BY r1.listing_id, r1.guest_name;

💡 Explanation

A self-join is just an ordinary JOIN where both sides come from the same table — reservations is aliased as r1 and r2 so each row can be matched against every other row from the same table, not just against itself.
r1.listing_id = r2.listing_id keeps the join to pairs on the same property — without it, every reservation would get paired with every other reservation across all listings, most of which have nothing to do with each other.
r1.reservation_id < r2.reservation_id does two jobs at once: it stops a reservation from being joined to itself (r1.reservation_id = r2.reservation_id would always match with an = condition), and it keeps each pair to a single row instead of two mirror-image rows (Aarav-Priya and Priya-Aarav).
Listing 200's two reservations (Meera and Rohan) show up here even though their actual stay dates don't overlap at all — this step only finds candidates that share a listing; it doesn't check dates yet.

Keep only pairs whose stay dates actually overlap

query.sql

SELECT r1.listing_id,
       r1.guest_name AS guest_1, r1.check_in AS check_in_1, r1.check_out AS check_out_1,
       r2.guest_name AS guest_2, r2.check_in AS check_in_2, r2.check_out AS check_out_2
FROM reservations r1
JOIN reservations r2
  ON r1.listing_id = r2.listing_id
 AND r1.reservation_id < r2.reservation_id
WHERE r1.check_in < COALESCE(r2.check_out, '9999-12-31')
  AND r2.check_in < COALESCE(r1.check_out, '9999-12-31')
ORDER BY r1.listing_id, r1.guest_name;

💡 Explanation

Two stays overlap exactly when each one starts before the other one ends — r1.check_in < r2.check_out AND r2.check_in < r1.check_out. Using strict < on both sides means a checkout date equal to the next check-in date does not count as overlapping, which is why Aarav and Karthik (08-05 to 08-05) correctly get excluded even though they're both candidates from the self-join.
COALESCE(check_out, '9999-12-31') stands in for 'no end date yet' with a date far enough in the future to never accidentally be earlier than a real check-in — that's what lets Divya's open-ended stay correctly overlap with Vikram's reservation three weeks later, instead of being silently dropped by a NULL comparison.
Listing 200 disappears from the result entirely: Meera (08-01 to 08-03) and Rohan (08-10 to 08-15) were a candidate pair after the self-join, but Rohan's check-in isn't before Meera's checkout, so the WHERE clause correctly filters them out as a non-overlap.
Three overlapping pairs come out of seven reservations across three listings: Aarav/Priya and Priya/Karthik on listing 100 (a three-way pileup, but reported as two separate pairs, not one group), and Divya/Vikram on listing 300 — each reported exactly once, in the order the self-join's inequality guaranteed.

▶ Practice this live ← Browse all questions