Airbnb: Attribute Each Booking to Its Last Search
Airbnb's search team credits each booking to the search that led to it, to measure which platform's search experience converts.
- Difficulty: Hard
- Topics: Window Functions, Joins, Dates
- Dialect: MySQL
- Problem: #572
Problem statement
Airbnb's search team credits each booking to the search that led to it, to measure which platform's search experience converts. The rule used here is last search wins: a booking is attributed to the guest's latest search for the same city made at or before booked_at and no more than 7 days before it (exactly 7 days still counts). If two searches share that latest time, the one with the higher search_id wins.
Return every booking as booking_id, search_id, platform and hours_to_book (whole hours from the search to the booking, TIMESTAMPDIFF(HOUR, …)). A booking with no qualifying search has NULL in the last three columns. Order the rows by booking_id.
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: SearchEvent
| Column | Type |
|---|---|
| search_id | int |
| guest_id | int |
| city | varchar |
| platform | enum(web, ios, android) |
| searched_at | datetime |
Primary key: search_id.
One row per search a guest ran for a destination.
Table: Booking
| Column | Type |
|---|---|
| booking_id | int |
| guest_id | int |
| city | varchar |
| booked_at | datetime |
Primary key: booking_id.
One row per confirmed booking; city is the booked listing's city.
Examples
Example 1
SearchEvent
| search_id | guest_id | city | platform | searched_at |
|---|---|---|---|---|
| 1 | 70 | Lisbon | web | 2025-04-01 09:00:00 |
| 2 | 70 | Lisbon | ios | 2025-04-03 20:00:00 |
| 3 | 70 | Porto | android | 2025-04-04 08:00:00 |
| 4 | 71 | Kyoto | web | 2025-04-02 10:00:00 |
| 5 | 71 | Kyoto | android | 2025-04-02 10:00:00 |
| 6 | 72 | Goa | ios | 2025-03-25 12:00:00 |
| 7 | 70 | Lisbon | web | 2025-04-05 07:00:00 |
Booking
| booking_id | guest_id | city | booked_at |
|---|---|---|---|
| 100 | 70 | Lisbon | 2025-04-04 21:30:00 |
| 101 | 71 | Kyoto | 2025-04-09 10:00:00 |
| 102 | 72 | Goa | 2025-04-02 12:00:01 |
| 103 | 73 | Bali | 2025-04-05 15:00:00 |
Output
| booking_id | search_id | platform | hours_to_book |
|---|---|---|---|
| 100 | 2 | ios | 25 |
| 101 | 5 | android | 168 |
| 102 | NULL | NULL | NULL |
| 103 | NULL | NULL | NULL |
How to solve Airbnb: Attribute Each Booking to Its Last Search
Attribution is a latest-before lookup with a window. The first step joins each booking to every search that could earn the credit: same guest, same city, searched_at no later than booked_at and no earlier than DATE_SUB(booked_at, INTERVAL 7 DAY). A search a minute after the booking, or seven days and a minute before, does not qualify; one exactly seven days before does.
Among a booking's candidates, ROW_NUMBER() OVER (PARTITION BY booking_id ORDER BY searched_at DESC, search_id DESC) puts the winner at position 1 — the latest search, and of two searches at the same second the higher id, which makes the result deterministic. RANK() would keep both twins and duplicate the booking.
Every booking must appear, so the final query starts from Booking and left-joins only the position-1 candidates; a booking with no qualifying search keeps NULLs in search_id, platform and hours_to_book. Putting pos = 1 in the ON clause rather than WHERE is what preserves those rows. The alternative finds the winning id with a correlated ORDER BY … LIMIT 1 subquery per booking and joins the search back for its platform; it reads more naturally but runs one lookup per booking, which an index on (guest_id, city, searched_at) makes cheap.
Reference solution (MySQL)
WITH candidates AS (
SELECT b.booking_id, s.search_id, s.platform,
TIMESTAMPDIFF(HOUR, s.searched_at, b.booked_at) AS hours_to_book,
ROW_NUMBER() OVER (PARTITION BY b.booking_id ORDER BY s.searched_at DESC, s.search_id DESC) AS pos
FROM Booking b
JOIN SearchEvent s
ON s.guest_id = b.guest_id AND s.city = b.city
AND s.searched_at <= b.booked_at
AND s.searched_at >= DATE_SUB(b.booked_at, INTERVAL 7 DAY)
)
SELECT b.booking_id, c.search_id, c.platform, c.hours_to_book
FROM Booking b
LEFT JOIN candidates c ON c.booking_id = b.booking_id AND c.pos = 1
ORDER BY b.booking_idAnother way
SELECT t.booking_id, s.search_id, s.platform, TIMESTAMPDIFF(HOUR, s.searched_at, t.booked_at) AS hours_to_book
FROM (
SELECT b.booking_id, b.booked_at,
(SELECT s2.search_id FROM SearchEvent s2
WHERE s2.guest_id = b.guest_id AND s2.city = b.city
AND s2.searched_at BETWEEN DATE_SUB(b.booked_at, INTERVAL 7 DAY) AND b.booked_at
ORDER BY s2.searched_at DESC, s2.search_id DESC
LIMIT 1) AS last_search
FROM Booking b
) t
LEFT JOIN SearchEvent s ON s.search_id = t.last_search
ORDER BY t.booking_id← Airbnb: Quote Stays From Nightly Prices and Discounts · Amazon: Lightning Deals That Sold Out Before Ending →