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
| Column | Type |
|---|---|
| event_id | int |
| guest_id | int |
| platform | enum(web, ios, android) |
| event_type | enum(search, listing_view, booking_request, booking_confirmed) |
| event_time | datetime |
Primary key: event_id.
One row per tracked event. A guest can repeat an event many times.
Examples
Example 1
GuestEvent
| event_id | guest_id | platform | event_type | event_time |
|---|---|---|---|---|
| 1 | 1 | web | search | 2025-04-01 10:00:00 |
| 2 | 1 | web | listing_view | 2025-04-01 10:02:00 |
| 3 | 1 | web | booking_confirmed | 2025-04-01 10:20:00 |
| 4 | 2 | web | search | 2025-04-01 11:00:00 |
| 5 | 2 | web | search | 2025-04-01 11:05:00 |
| 6 | 3 | ios | search | 2025-04-02 08:00:00 |
| 7 | 3 | ios | listing_view | 2025-04-02 08:03:00 |
| 8 | 3 | web | booking_confirmed | 2025-04-02 21:00:00 |
| 9 | 4 | android | listing_view | 2025-04-03 09:00:00 |
| 10 | 4 | android | booking_confirmed | 2025-04-03 09:30:00 |
Output
| platform | searchers | viewers | bookers | conversion_pct |
|---|---|---|---|---|
| ios | 1 | 1 | 0 | 0 |
| web | 2 | 1 | 1 | 50 |
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) > 0Another 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 →