Airbnb: Attribute Each Booking to Its Last Search

Hard
572.

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

ColumnTypeValues
search_idint
guest_idint
cityvarchar
platformenumweb, ios, android
searched_atdatetime

Primary key: search_id.

One row per search a guest ran for a destination.

Table: Booking

ColumnType
booking_idint
guest_idint
cityvarchar
booked_atdatetime

Primary key: booking_id.

One row per confirmed booking; city is the booked listing's city.

Example

The rows must come back in the order shown.

SearchEvent

search_idguest_idcityplatformsearched_at
170Lisbonweb2025-04-01 09:00:00
270Lisbonios2025-04-03 20:00:00
370Portoandroid2025-04-04 08:00:00
471Kyotoweb2025-04-02 10:00:00
571Kyotoandroid2025-04-02 10:00:00
672Goaios2025-03-25 12:00:00
770Lisbonweb2025-04-05 07:00:00

Booking

booking_idguest_idcitybooked_at
10070Lisbon2025-04-04 21:30:00
10171Kyoto2025-04-09 10:00:00
10272Goa2025-04-02 12:00:01
10373Bali2025-04-05 15:00:00

Output

booking_idsearch_idplatformhours_to_book
1002ios25
1015android168
102NULLNULLNULL
103NULLNULLNULL
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: 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

ColumnType
search_idint
guest_idint
cityvarchar
platformenum(web, ios, android)
searched_atdatetime

Primary key: search_id.

One row per search a guest ran for a destination.

Table: Booking

ColumnType
booking_idint
guest_idint
cityvarchar
booked_atdatetime

Primary key: booking_id.

One row per confirmed booking; city is the booked listing's city.

Examples

Example 1

SearchEvent

search_idguest_idcityplatformsearched_at
170Lisbonweb2025-04-01 09:00:00
270Lisbonios2025-04-03 20:00:00
370Portoandroid2025-04-04 08:00:00
471Kyotoweb2025-04-02 10:00:00
571Kyotoandroid2025-04-02 10:00:00
672Goaios2025-03-25 12:00:00
770Lisbonweb2025-04-05 07:00:00

Booking

booking_idguest_idcitybooked_at
10070Lisbon2025-04-04 21:30:00
10171Kyoto2025-04-09 10:00:00
10272Goa2025-04-02 12:00:01
10373Bali2025-04-05 15:00:00

Output

booking_idsearch_idplatformhours_to_book
1002ios25
1015android168
102NULLNULLNULL
103NULLNULLNULL

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_id

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