Airbnb: Hosts With Three Cancellations Inside 30 Days

Hard
569.

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

ColumnTypeValues
reservation_idint
host_idint
statusenumcompleted, cancelled_by_guest, cancelled_by_host
cancelled_ondate

Primary key: reservation_id.

cancelled_on is set for both kinds of cancellation and NULL for a completed stay.

Example

The rows must come back in the order shown.

Reservation

reservation_idhost_idstatuscancelled_on
15cancelled_by_host2025-01-03
25cancelled_by_host2025-01-20
35cancelled_by_guest2025-01-25
45cancelled_by_host2025-02-02
55cancelled_by_host2025-02-10
66cancelled_by_host2025-01-01
76cancelled_by_host2025-01-15
86cancelled_by_host2025-02-01
97cancelled_by_host2025-03-01
107cancelled_by_host2025-03-01
2 more rows

Output

host_idflagged_on
52025-02-02
72025-03-01
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: 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

ColumnType
reservation_idint
host_idint
statusenum(completed, cancelled_by_guest, cancelled_by_host)
cancelled_ondate

Primary key: reservation_id.

cancelled_on is set for both kinds of cancellation and NULL for a completed stay.

Examples

Example 1

Reservation

reservation_idhost_idstatuscancelled_on
15cancelled_by_host2025-01-03
25cancelled_by_host2025-01-20
35cancelled_by_guest2025-01-25
45cancelled_by_host2025-02-02
55cancelled_by_host2025-02-10
66cancelled_by_host2025-01-01
76cancelled_by_host2025-01-15
86cancelled_by_host2025-02-01
97cancelled_by_host2025-03-01
107cancelled_by_host2025-03-01
117cancelled_by_host2025-03-01
127completedNULL

Output

host_idflagged_on
52025-02-02
72025-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_id

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