Airbnb: Experience Bookings and Guests per Category
Besides homes, Airbnb sells Experiences — cooking classes, hikes, studio visits — led by local hosts.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #552
Problem statement
Besides homes, Airbnb sells Experiences — cooking classes, hikes, studio visits — led by local hosts. The Experiences team wants to know which categories guests actually turn up for.
For every category with at least one booking that was not cancelled, return category, bookings (the number of non-cancelled bookings) and guests (the total guests on them). Categories whose bookings were all cancelled, or that have none, do not appear. Return the rows 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: Experience
| Column | Type |
|---|---|
| experience_id | int |
| title | varchar |
| category | enum(food, nature, art, sports, wellness) |
| city | varchar |
Primary key: experience_id.
One row per Experience a host offers.
Table: ExperienceBooking
| Column | Type |
|---|---|
| booking_id | int |
| experience_id | int |
| guests | int |
| status | enum(confirmed, attended, cancelled) |
Primary key: booking_id.
One booking can bring several guests. experience_id always names an Experience.
Examples
Example 1
Experience
| experience_id | title | category | city |
|---|---|---|---|
| 1 | Pastel de nata workshop | food | Lisbon |
| 2 | Sunrise volcano hike | nature | Bali |
| 3 | Tea ceremony | art | Kyoto |
| 4 | Spice farm lunch | food | Goa |
| 5 | Sunset surf lesson | sports | Bali |
ExperienceBooking
| booking_id | experience_id | guests | status |
|---|---|---|---|
| 101 | 1 | 2 | attended |
| 102 | 1 | 4 | confirmed |
| 103 | 4 | 1 | attended |
| 104 | 2 | 3 | attended |
| 105 | 2 | 2 | cancelled |
| 106 | 3 | 2 | cancelled |
Output
| category | bookings | guests |
|---|---|---|
| food | 3 | 7 |
| nature | 1 | 3 |
How to solve Airbnb: Experience Bookings and Guests per Category
The booking rows hold the numbers and the Experience rows hold the category, so the query joins them on experience_id, drops cancelled bookings in WHERE, and groups by category. Inside each group COUNT(*) is the number of bookings and SUM(guests) the people they bring — a booking for four is one booking but four guests.
Filtering before grouping is what makes the exclusions right. A category whose bookings were all cancelled loses every row in WHERE and never forms a group; a category with no bookings has no join partner at all. Neither appears, exactly as asked — a HAVING is not needed.
The alternative pre-aggregates the bookings per Experience in a derived table and then adds those subtotals per category. It returns the same numbers and is the shape to reach for when the detail table is large, because it groups the big table once by its own key before joining. Both are linear with an index on experience_id.
Reference solution (MySQL)
SELECT e.category, COUNT(*) AS bookings, SUM(b.guests) AS guests
FROM Experience e
JOIN ExperienceBooking b ON b.experience_id = e.experience_id
WHERE b.status <> 'cancelled'
GROUP BY e.categoryAnother way
SELECT e.category, SUM(t.n) AS bookings, SUM(t.g) AS guests
FROM Experience e
JOIN (SELECT experience_id, COUNT(*) AS n, SUM(guests) AS g FROM ExperienceBooking
WHERE status IN ('confirmed', 'attended') GROUP BY experience_id) t ON t.experience_id = e.experience_id
GROUP BY e.category← Airbnb: Reviews Where Cleanliness Trails the Overall Rating · Airbnb: Friday and Saturday Check-Ins per City →