Airbnb: Guests Who Came Back to the Same Host Within a Year
Repeat guests are a sign of a great host. A host-insights analyst wants every guest who returned to the same host — possibly at a different listing of that…
- Difficulty: Medium
- Topics: Joins, Dates, Subqueries
- Dialect: MySQL
- Problem: #560
Problem statement
Repeat guests are a sign of a great host. A host-insights analyst wants every guest who returned to the same host — possibly at a different listing of that host — within a year.
A guest has returned to a host when, among the guest's completed stays at that host's listings, there is a later stay whose check_in is on or after an earlier stay's check_out and at most 365 days after it. Return each such pair once as guest_id and host_id, in any order. Cancelled reservations never count.
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: Listing
| Column | Type |
|---|---|
| listing_id | int |
| host_id | int |
| city | varchar |
Primary key: listing_id.
A host can own several listings.
Table: Reservation
| Column | Type |
|---|---|
| reservation_id | int |
| guest_id | int |
| listing_id | int |
| check_in | date |
| check_out | date |
| status | enum(completed, cancelled) |
Primary key: reservation_id.
check_out is always after check_in; listing_id always names a listing.
Examples
Example 1
Listing
| listing_id | host_id | city |
|---|---|---|
| 1 | 50 | Lisbon |
| 2 | 50 | Lisbon |
| 3 | 60 | Goa |
| 4 | 70 | Kyoto |
Reservation
| reservation_id | guest_id | listing_id | check_in | check_out | status |
|---|---|---|---|---|---|
| 1 | 900 | 1 | 2024-02-01 | 2024-02-05 | completed |
| 2 | 900 | 2 | 2024-11-20 | 2024-11-23 | completed |
| 3 | 901 | 3 | 2024-01-10 | 2024-01-12 | completed |
| 4 | 901 | 3 | 2025-01-12 | 2025-01-15 | completed |
| 5 | 902 | 3 | 2024-03-01 | 2024-03-04 | completed |
| 6 | 902 | 3 | 2025-03-05 | 2025-03-07 | completed |
| 7 | 903 | 4 | 2024-05-01 | 2024-05-03 | completed |
| 8 | 903 | 4 | 2024-06-01 | 2024-06-04 | cancelled |
Output
| guest_id | host_id |
|---|---|
| 900 | 50 |
How to solve Airbnb: Guests Who Came Back to the Same Host Within a Year
The question compares two stays of one guest, so it is a self join on Reservation: a is the earlier stay and b the return. The host is not on the reservation, so each side joins its own Listing row, and the pair must share host_id — which is how a return to a different listing of the same host still counts.
The date rule has two halves. b.check_in >= a.check_out makes b genuinely later (a guest can check in the day another stay ends, so equality is allowed), and DATEDIFF(b.check_in, a.check_out) <= 365 keeps it within a year — exactly 365 days counts, 366 does not. Both stays must be completed; a cancelled booking is not a visit. b.reservation_id <> a.reservation_id stops a stay pairing with itself.
A guest with three visits makes several qualifying pairs, so DISTINCT returns each guest–host pair once. The alternative builds the completed stays with their host in a CTE and asks EXISTS for a return, using BETWEEN check_out AND DATE_ADD(check_out, INTERVAL 365 DAY). With indexes on (guest_id) the pairing stays small, since only one guest's stays are compared at a time.
Reference solution (MySQL)
SELECT DISTINCT a.guest_id, la.host_id
FROM Reservation a
JOIN Listing la ON la.listing_id = a.listing_id
JOIN Reservation b ON b.guest_id = a.guest_id AND b.reservation_id <> a.reservation_id
JOIN Listing lb ON lb.listing_id = b.listing_id AND lb.host_id = la.host_id
WHERE a.status = 'completed' AND b.status = 'completed'
AND b.check_in >= a.check_out
AND DATEDIFF(b.check_in, a.check_out) <= 365Another way
WITH s AS (
SELECT r.reservation_id, r.guest_id, l.host_id, r.check_in, r.check_out
FROM Reservation r JOIN Listing l ON l.listing_id = r.listing_id
WHERE r.status = 'completed'
)
SELECT DISTINCT a.guest_id, a.host_id FROM s a
WHERE EXISTS (
SELECT 1 FROM s b
WHERE b.guest_id = a.guest_id AND b.host_id = a.host_id AND b.reservation_id <> a.reservation_id
AND b.check_in BETWEEN a.check_out AND DATE_ADD(a.check_out, INTERVAL 365 DAY)
)← Airbnb: Most-Wishlisted Listing in Each City · Airbnb: Rank Experiences by a Bayesian Average Rating →