Airbnb: Experience Bookings and Guests per Category

Easy
552.

Airbnb: Experience Bookings and Guests per Category

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

ColumnTypeValues
experience_idint
titlevarchar
categoryenumfood, nature, art, sports, wellness
cityvarchar

Primary key: experience_id.

One row per Experience a host offers.

Table: ExperienceBooking

ColumnTypeValues
booking_idint
experience_idint
guestsint
statusenumconfirmed, attended, cancelled

Primary key: booking_id.

One booking can bring several guests. experience_id always names an Experience.

Example

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

Experience

experience_idtitlecategorycity
1Pastel de nata workshopfoodLisbon
2Sunrise volcano hikenatureBali
3Tea ceremonyartKyoto
4Spice farm lunchfoodGoa
5Sunset surf lessonsportsBali

ExperienceBooking

booking_idexperience_idguestsstatus
10112attended
10214confirmed
10341attended
10423attended
10522cancelled
10632cancelled

Output

categorybookingsguests
food37
nature13
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: 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

ColumnType
experience_idint
titlevarchar
categoryenum(food, nature, art, sports, wellness)
cityvarchar

Primary key: experience_id.

One row per Experience a host offers.

Table: ExperienceBooking

ColumnType
booking_idint
experience_idint
guestsint
statusenum(confirmed, attended, cancelled)

Primary key: booking_id.

One booking can bring several guests. experience_id always names an Experience.

Examples

Example 1

Experience

experience_idtitlecategorycity
1Pastel de nata workshopfoodLisbon
2Sunrise volcano hikenatureBali
3Tea ceremonyartKyoto
4Spice farm lunchfoodGoa
5Sunset surf lessonsportsBali

ExperienceBooking

booking_idexperience_idguestsstatus
10112attended
10214confirmed
10341attended
10423attended
10522cancelled
10632cancelled

Output

categorybookingsguests
food37
nature13

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

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