Airbnb: Guests Who Came Back to the Same Host Within a Year

Medium
560.

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

ColumnType
listing_idint
host_idint
cityvarchar

Primary key: listing_id.

A host can own several listings.

Table: Reservation

ColumnTypeValues
reservation_idint
guest_idint
listing_idint
check_indate
check_outdate
statusenumcompleted, cancelled

Primary key: reservation_id.

check_out is always after check_in; listing_id always names a listing.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Listing

listing_idhost_idcity
150Lisbon
250Lisbon
360Goa
470Kyoto

Reservation

reservation_idguest_idlisting_idcheck_incheck_outstatus
190012024-02-012024-02-05completed
290022024-11-202024-11-23completed
390132024-01-102024-01-12completed
490132025-01-122025-01-15completed
590232024-03-012024-03-04completed
690232025-03-052025-03-07completed
790342024-05-012024-05-03completed
890342024-06-012024-06-04cancelled

Output

guest_idhost_id
90050
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: 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

ColumnType
listing_idint
host_idint
cityvarchar

Primary key: listing_id.

A host can own several listings.

Table: Reservation

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

Primary key: reservation_id.

check_out is always after check_in; listing_id always names a listing.

Examples

Example 1

Listing

listing_idhost_idcity
150Lisbon
250Lisbon
360Goa
470Kyoto

Reservation

reservation_idguest_idlisting_idcheck_incheck_outstatus
190012024-02-012024-02-05completed
290022024-11-202024-11-23completed
390132024-01-102024-01-12completed
490132025-01-122025-01-15completed
590232024-03-012024-03-04completed
690232025-03-052025-03-07completed
790342024-05-012024-05-03completed
890342024-06-012024-06-04cancelled

Output

guest_idhost_id
90050

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) <= 365

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