Airbnb: Guest Cancellation Rate by Cancellation Policy
Hosts on Airbnb choose a cancellation policy, and the policy team wants to know whether stricter policies really mean fewer guest cancellations.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #562
Problem statement
Hosts on Airbnb choose a cancellation policy, and the policy team wants to know whether stricter policies really mean fewer guest cancellations.
For every policy in CancellationPolicy, return policy, reservations (all reservations made under it, whatever their status), guest_cancellations (those with status cancelled_by_guest) and cancel_rate_pct = 100 × guest_cancellations ÷ reservations rounded to 2 decimal places. A cancellation by the host is a reservation but not a guest cancellation. A policy with no reservations shows 0, 0 and a NULL rate. Order the rows by policy.
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: CancellationPolicy
| Column | Type |
|---|---|
| policy | varchar |
| full_refund_days | int |
Primary key: policy.
The policies a host can choose; full_refund_days is how many days before check-in a guest can still cancel for a full refund (an invented rule set).
Table: Reservation
| Column | Type |
|---|---|
| reservation_id | int |
| policy | varchar |
| status | enum(upcoming, completed, cancelled_by_guest, cancelled_by_host) |
Primary key: reservation_id.
policy is the policy in force when the reservation was made; it always names a row of CancellationPolicy.
Examples
Example 1
CancellationPolicy
| policy | full_refund_days |
|---|---|
| firm | 30 |
| flexible | 1 |
| moderate | 5 |
| strict | 14 |
Reservation
| reservation_id | policy | status |
|---|---|---|
| 1 | flexible | completed |
| 2 | flexible | cancelled_by_guest |
| 3 | flexible | cancelled_by_guest |
| 4 | flexible | upcoming |
| 5 | moderate | completed |
| 6 | moderate | cancelled_by_host |
| 7 | strict | completed |
| 8 | strict | cancelled_by_guest |
| 9 | strict | completed |
Output
| policy | reservations | guest_cancellations | cancel_rate_pct |
|---|---|---|---|
| firm | 0 | 0 | NULL |
| flexible | 4 | 2 | 50 |
| moderate | 2 | 0 | 0 |
| strict | 3 | 1 | 33.33 |
How to solve Airbnb: Guest Cancellation Rate by Cancellation Policy
"For every policy" means the policy table drives the query: CancellationPolicy LEFT JOIN Reservation, grouped by policy. A policy with no reservations still produces one row, with the reservation columns NULL.
That NULL row is why the counts are written carefully. COUNT(*) counts rows, so it would report 1 reservation for an unbooked policy; COUNT(r.reservation_id) counts non-NULL ids and correctly gives 0. Likewise COUNT(CASE WHEN status = 'cancelled_by_guest' THEN 1 END) gives 0 where a SUM(CASE …) without an ELSE would give NULL. Host cancellations fall in the total but not in the guest count, as the statement asks.
The rate divides by the reservation count, which can be 0. NULLIF(count, 0) turns that into NULL, and any arithmetic with NULL is NULL — the required result, and no division-by-zero surprise. The alternative counts with two correlated subqueries per policy and handles zero with a CASE. The join version reads Reservation once.
Reference solution (MySQL)
SELECT p.policy,
COUNT(r.reservation_id) AS reservations,
COUNT(CASE WHEN r.status = 'cancelled_by_guest' THEN 1 END) AS guest_cancellations,
ROUND(100 * COUNT(CASE WHEN r.status = 'cancelled_by_guest' THEN 1 END) / NULLIF(COUNT(r.reservation_id), 0), 2) AS cancel_rate_pct
FROM CancellationPolicy p
LEFT JOIN Reservation r ON r.policy = p.policy
GROUP BY p.policy
ORDER BY p.policyAnother way
SELECT policy, reservations, guest_cancellations,
CASE WHEN reservations = 0 THEN NULL ELSE ROUND(100 * guest_cancellations / reservations, 2) END AS cancel_rate_pct
FROM (
SELECT p.policy,
(SELECT COUNT(*) FROM Reservation r WHERE r.policy = p.policy) AS reservations,
(SELECT COUNT(*) FROM Reservation r WHERE r.policy = p.policy AND r.status = 'cancelled_by_guest') AS guest_cancellations
FROM CancellationPolicy p
) t
ORDER BY policy← Airbnb: Rank Experiences by a Bayesian Average Rating · Airbnb: Listings With a Kitchen but No Wi-Fi →