Airbnb: Hosts With Three Cancellations Inside 30 Days
A host cancelling on a guest is one of the worst experiences on Airbnb, and the trust team reviews hosts who do it repeatedly.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #569
Problem statement
A host cancelling on a guest is one of the worst experiences on Airbnb, and the trust team reviews hosts who do it repeatedly. In this exercise a host is flagged once they have three host cancellations within 30 days — the third falls no more than 30 days after the first (DATEDIFF ≤ 30).
Only reservations with status cancelled_by_host count; their date is cancelled_on. For every flagged host, return host_id and flagged_on — the earliest date on which they reached three host cancellations within the preceding 30 days. Several cancellations can share a day. Order the rows by host_id.
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: Reservation
| Column | Type |
|---|---|
| reservation_id | int |
| host_id | int |
| status | enum(completed, cancelled_by_guest, cancelled_by_host) |
| cancelled_on | date |
Primary key: reservation_id.
cancelled_on is set for both kinds of cancellation and NULL for a completed stay.
Examples
Example 1
Reservation
| reservation_id | host_id | status | cancelled_on |
|---|---|---|---|
| 1 | 5 | cancelled_by_host | 2025-01-03 |
| 2 | 5 | cancelled_by_host | 2025-01-20 |
| 3 | 5 | cancelled_by_guest | 2025-01-25 |
| 4 | 5 | cancelled_by_host | 2025-02-02 |
| 5 | 5 | cancelled_by_host | 2025-02-10 |
| 6 | 6 | cancelled_by_host | 2025-01-01 |
| 7 | 6 | cancelled_by_host | 2025-01-15 |
| 8 | 6 | cancelled_by_host | 2025-02-01 |
| 9 | 7 | cancelled_by_host | 2025-03-01 |
| 10 | 7 | cancelled_by_host | 2025-03-01 |
| 11 | 7 | cancelled_by_host | 2025-03-01 |
| 12 | 7 | completed | NULL |
Output
| host_id | flagged_on |
|---|---|
| 5 | 2025-02-02 |
| 7 | 2025-03-01 |
How to solve Airbnb: Hosts With Three Cancellations Inside 30 Days
"Three events inside any 30-day window" sounds like it needs every window, but sorting reduces it to neighbours. Put a host's cancellations in date order. If any three fall within 30 days, then the last of them together with the two cancellations immediately before it in the sorted list also fall within 30 days — those two are at least as late as the original pair. So it is enough to compare each cancellation with the one two places earlier: LAG(cancelled_on, 2).
Every cancellation whose date is at most 30 days after its second predecessor completes a window, and the earliest such date per host is flagged_on. Same-day cancellations are ordered by reservation_id so the window has a fixed order, but the answer is a date, which the tie-break cannot change. The first two cancellations of a host have no second predecessor (NULL), so DATEDIFF is NULL and they are skipped. Guest cancellations and completed stays never enter.
The alternative counts directly: for each host cancellation, how many host cancellations fall in the 30 days up to and including its date (BETWEEN DATE_SUB(…, INTERVAL 30 DAY) AND …); three or more flags it. That is a correlated count per row — quadratic per host — where the LAG version is one sort.
Reference solution (MySQL)
WITH hc AS (
SELECT host_id, cancelled_on,
LAG(cancelled_on, 2) OVER (PARTITION BY host_id ORDER BY cancelled_on, reservation_id) AS two_before
FROM Reservation
WHERE status = 'cancelled_by_host'
)
SELECT host_id, MIN(cancelled_on) AS flagged_on
FROM hc
WHERE DATEDIFF(cancelled_on, two_before) <= 30
GROUP BY host_id
ORDER BY host_idAnother way
WITH hc AS (SELECT reservation_id, host_id, cancelled_on FROM Reservation WHERE status = 'cancelled_by_host')
SELECT a.host_id, MIN(a.cancelled_on) AS flagged_on
FROM hc a
WHERE (SELECT COUNT(*) FROM hc b
WHERE b.host_id = a.host_id
AND b.cancelled_on BETWEEN DATE_SUB(a.cancelled_on, INTERVAL 30 DAY) AND a.cancelled_on) >= 3
GROUP BY a.host_id
ORDER BY a.host_id← Airbnb: Back-to-Back Stays Merged Into Trips · Airbnb: Reconcile Host Payouts Against Earnings →