Airbnb: Search-to-Booking Funnel by Platform

Medium
558.

Airbnb: Search-to-Booking Funnel by Platform

A product analyst on Airbnb's search team compares how guests move from searching to booking on the website and the two mobile apps. Every event carries the platform it happened on, and a funnel is counted per platform — a guest who searched on iOS and booked on the web does not convert on either.

For each platform: searchers = distinct guests with a search on it; viewers = of those, the guests who also have a listing_view on it; bookers = of those viewers, the guests who also have a booking_confirmed on it. booking_request events are not a funnel step. Return platform, searchers, viewers, bookers and conversion_pct = 100 × bookers ÷ searchers rounded to 2 decimal places, for every platform with at least one searcher, in any order.

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

ColumnTypeValues
event_idint
guest_idint
platformenumweb, ios, android
event_typeenumsearch, listing_view, booking_request, booking_confirmed
event_timedatetime

Primary key: event_id.

One row per tracked event. A guest can repeat an event many times.

Example

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

GuestEvent

event_idguest_idplatformevent_typeevent_time
11websearch2025-04-01 10:00:00
21weblisting_view2025-04-01 10:02:00
31webbooking_confirmed2025-04-01 10:20:00
42websearch2025-04-01 11:00:00
52websearch2025-04-01 11:05:00
63iossearch2025-04-02 08:00:00
73ioslisting_view2025-04-02 08:03:00
83webbooking_confirmed2025-04-02 21:00:00
94androidlisting_view2025-04-03 09:00:00
104androidbooking_confirmed2025-04-03 09:30:00

Output

platformsearchersviewersbookersconversion_pct
ios1100
web21150
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: Search-to-Booking Funnel by Platform — SQL Problem

A product analyst on Airbnb's search team compares how guests move from searching to booking on the website and the two mobile apps.

  • Difficulty: Medium
  • Topics: Aggregation, Conditional Logic
  • Dialect: MySQL
  • Problem: #558

Problem statement

A product analyst on Airbnb's search team compares how guests move from searching to booking on the website and the two mobile apps. Every event carries the platform it happened on, and a funnel is counted per platform — a guest who searched on iOS and booked on the web does not convert on either.

For each platform: searchers = distinct guests with a search on it; viewers = of those, the guests who also have a listing_view on it; bookers = of those viewers, the guests who also have a booking_confirmed on it. booking_request events are not a funnel step. Return platform, searchers, viewers, bookers and conversion_pct = 100 × bookers ÷ searchers rounded to 2 decimal places, for every platform with at least one searcher, in any order.

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

ColumnType
event_idint
guest_idint
platformenum(web, ios, android)
event_typeenum(search, listing_view, booking_request, booking_confirmed)
event_timedatetime

Primary key: event_id.

One row per tracked event. A guest can repeat an event many times.

Examples

Example 1

GuestEvent

event_idguest_idplatformevent_typeevent_time
11websearch2025-04-01 10:00:00
21weblisting_view2025-04-01 10:02:00
31webbooking_confirmed2025-04-01 10:20:00
42websearch2025-04-01 11:00:00
52websearch2025-04-01 11:05:00
63iossearch2025-04-02 08:00:00
73ioslisting_view2025-04-02 08:03:00
83webbooking_confirmed2025-04-02 21:00:00
94androidlisting_view2025-04-03 09:00:00
104androidbooking_confirmed2025-04-03 09:30:00

Output

platformsearchersviewersbookersconversion_pct
ios1100
web21150

How to solve Airbnb: Search-to-Booking Funnel by Platform

A funnel counts people, not events, and each step requires the steps before it. So the first move is to collapse the event stream to one row per (platform, guest_id) with a 0/1 flag per step: MAX(CASE WHEN event_type = 'search' THEN 1 ELSE 0 END) is 1 if the guest searched there at least once, however many times. Repeats disappear at this point.

Then each stage is a sum of flags. SUM(s) counts searchers; SUM(s * v) counts guests who both searched and viewed (a product of 0/1 flags is an AND); SUM(s * v * b) adds the confirmed booking. A guest who viewed and booked on Android without searching there contributes nothing — the example's guest 4 — and neither does guest 3, who searched on iOS but booked on the web. HAVING SUM(s) > 0 drops a platform nobody searched on, which also keeps the division safe.

The alternative starts from the search events and left-joins the distinct viewers and bookers of the same platform, counting with COUNT(DISTINCT CASE …). It is the same set logic written as joins; the flag version reads the table once and is usually cheaper.

Reference solution (MySQL)

WITH per_guest AS (
  SELECT platform, guest_id,
         MAX(CASE WHEN event_type = 'search' THEN 1 ELSE 0 END) AS s,
         MAX(CASE WHEN event_type = 'listing_view' THEN 1 ELSE 0 END) AS v,
         MAX(CASE WHEN event_type = 'booking_confirmed' THEN 1 ELSE 0 END) AS b
  FROM GuestEvent
  GROUP BY platform, guest_id
)
SELECT platform,
       SUM(s) AS searchers,
       SUM(s * v) AS viewers,
       SUM(s * v * b) AS bookers,
       ROUND(100 * SUM(s * v * b) / SUM(s), 2) AS conversion_pct
FROM per_guest
GROUP BY platform
HAVING SUM(s) > 0

Another way

SELECT e.platform,
       COUNT(DISTINCT e.guest_id) AS searchers,
       COUNT(DISTINCT CASE WHEN v.guest_id IS NOT NULL THEN e.guest_id END) AS viewers,
       COUNT(DISTINCT CASE WHEN v.guest_id IS NOT NULL AND b.guest_id IS NOT NULL THEN e.guest_id END) AS bookers,
       ROUND(100 * COUNT(DISTINCT CASE WHEN v.guest_id IS NOT NULL AND b.guest_id IS NOT NULL THEN e.guest_id END) / COUNT(DISTINCT e.guest_id), 2) AS conversion_pct
FROM GuestEvent e
LEFT JOIN (SELECT DISTINCT platform, guest_id FROM GuestEvent WHERE event_type = 'listing_view') v
  ON v.platform = e.platform AND v.guest_id = e.guest_id
LEFT JOIN (SELECT DISTINCT platform, guest_id FROM GuestEvent WHERE event_type = 'booking_confirmed') b
  ON b.platform = e.platform AND b.guest_id = e.guest_id
WHERE e.event_type = 'search'
GROUP BY e.platform

← Airbnb: Host Response Rate Within 24 Hours · Airbnb: Most-Wishlisted Listing in Each City →