Airbnb: Guest Cancellation Rate by Cancellation Policy

Medium
562.

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.

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

ColumnType
policyvarchar
full_refund_daysint

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

ColumnTypeValues
reservation_idint
policyvarchar
statusenumupcoming, 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.

Example

The rows must come back in the order shown.

CancellationPolicy

policyfull_refund_days
firm30
flexible1
moderate5
strict14

Reservation

reservation_idpolicystatus
1flexiblecompleted
2flexiblecancelled_by_guest
3flexiblecancelled_by_guest
4flexibleupcoming
5moderatecompleted
6moderatecancelled_by_host
7strictcompleted
8strictcancelled_by_guest
9strictcompleted

Output

policyreservationsguest_cancellationscancel_rate_pct
firm00NULL
flexible4250
moderate200
strict3133.33
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: 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

ColumnType
policyvarchar
full_refund_daysint

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

ColumnType
reservation_idint
policyvarchar
statusenum(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

policyfull_refund_days
firm30
flexible1
moderate5
strict14

Reservation

reservation_idpolicystatus
1flexiblecompleted
2flexiblecancelled_by_guest
3flexiblecancelled_by_guest
4flexibleupcoming
5moderatecompleted
6moderatecancelled_by_host
7strictcompleted
8strictcancelled_by_guest
9strictcompleted

Output

policyreservationsguest_cancellationscancel_rate_pct
firm00NULL
flexible4250
moderate200
strict3133.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.policy

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