Airbnb: Reconcile Host Payouts Against Earnings
A payments engineer at Airbnb runs a nightly reconciliation between what each reservation earned the host and the payouts actually sent.
- Difficulty: Hard
- Topics: Joins, Aggregation, Subqueries, Conditional Logic
- Dialect: MySQL
- Problem: #570
Problem statement
A payments engineer at Airbnb runs a nightly reconciliation between what each reservation earned the host and the payouts actually sent. A reservation can be paid in several transfers, and transfers can fail.
Only payouts with status sent count as paid. A completed reservation is short when its sent payouts add up to less than its host_earnings (no payout at all is short too); the shortfall is the difference. A cancelled reservation is overpaid when it has any sent payout; the overpayment is the whole sent amount. Upcoming reservations are ignored. For every host with at least one short or overpaid reservation, return host_id, short_reservations, short_amount, overpaid_reservations and overpaid_amount, ordered 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(upcoming, completed, cancelled) |
| host_earnings | int |
Primary key: reservation_id.
host_earnings is what the host is owed for the stay, in US dollars.
Table: Payout
| Column | Type |
|---|---|
| payout_id | int |
| reservation_id | int |
| amount | int |
| status | enum(sent, pending, failed) |
Primary key: payout_id.
One transfer towards a reservation; reservation_id always names a reservation.
Examples
Example 1
Reservation
| reservation_id | host_id | status | host_earnings |
|---|---|---|---|
| 1 | 10 | completed | 500 |
| 2 | 10 | completed | 300 |
| 3 | 10 | cancelled | 200 |
| 4 | 20 | completed | 400 |
| 5 | 20 | upcoming | 250 |
| 6 | 30 | completed | 350 |
| 7 | 30 | cancelled | 150 |
Payout
| payout_id | reservation_id | amount | status |
|---|---|---|---|
| 1 | 1 | 300 | sent |
| 2 | 1 | 200 | sent |
| 3 | 2 | 300 | failed |
| 4 | 2 | 100 | sent |
| 5 | 3 | 200 | sent |
| 6 | 4 | 400 | sent |
| 7 | 5 | 250 | pending |
| 8 | 6 | 350 | pending |
| 9 | 7 | 150 | failed |
Output
| host_id | short_reservations | short_amount | overpaid_reservations | overpaid_amount |
|---|---|---|---|---|
| 10 | 1 | 200 | 1 | 200 |
| 30 | 1 | 350 | 0 | 0 |
How to solve Airbnb: Reconcile Host Payouts Against Earnings
Reconciliation compares two grains — one row per reservation against many payouts per reservation — and the classic bug is to join them directly and sum: a reservation paid in two transfers then counts its host_earnings twice. So the payouts are aggregated to the reservation first: the paid CTE sums only sent transfers per reservation (failed and pending ones are not money in the host's account).
Then every reservation is left-joined to its paid total, with COALESCE(…, 0) for reservations that have no sent transfer — those are exactly the "no payout at all" short cases, which an inner join would silently drop. Now each reservation is one row with host_earnings and sent, and the four figures are conditional sums per host: a completed reservation with sent < host_earnings counts as short and adds its difference; a cancelled one with sent > 0 counts as overpaid and adds what was sent. Upcoming reservations meet no condition and add zeros. HAVING keeps hosts with at least one problem.
The alternative computes sent with a correlated subquery per reservation and turns the two conditions into 0/1 columns with IF, multiplying them into the amounts. Both avoid the fan-out; the CTE version reads Payout once.
Reference solution (MySQL)
WITH paid AS (
SELECT reservation_id, SUM(amount) AS sent
FROM Payout
WHERE status = 'sent'
GROUP BY reservation_id
), r AS (
SELECT res.host_id, res.status, res.host_earnings, COALESCE(p.sent, 0) AS sent
FROM Reservation res
LEFT JOIN paid p ON p.reservation_id = res.reservation_id
)
SELECT host_id,
SUM(CASE WHEN status = 'completed' AND sent < host_earnings THEN 1 ELSE 0 END) AS short_reservations,
SUM(CASE WHEN status = 'completed' AND sent < host_earnings THEN host_earnings - sent ELSE 0 END) AS short_amount,
SUM(CASE WHEN status = 'cancelled' AND sent > 0 THEN 1 ELSE 0 END) AS overpaid_reservations,
SUM(CASE WHEN status = 'cancelled' THEN sent ELSE 0 END) AS overpaid_amount
FROM r
GROUP BY host_id
HAVING SUM(CASE WHEN status = 'completed' AND sent < host_earnings THEN 1 ELSE 0 END)
+ SUM(CASE WHEN status = 'cancelled' AND sent > 0 THEN 1 ELSE 0 END) > 0
ORDER BY host_idAnother way
SELECT host_id,
SUM(is_short) AS short_reservations, SUM(is_short * (host_earnings - sent)) AS short_amount,
SUM(is_over) AS overpaid_reservations, SUM(is_over * sent) AS overpaid_amount
FROM (
SELECT t.*,
IF(status = 'completed' AND sent < host_earnings, 1, 0) AS is_short,
IF(status = 'cancelled' AND sent > 0, 1, 0) AS is_over
FROM (
SELECT res.host_id, res.status, res.host_earnings,
(SELECT IFNULL(SUM(p.amount), 0) FROM Payout p WHERE p.reservation_id = res.reservation_id AND p.status = 'sent') AS sent
FROM Reservation res
) t
) x
GROUP BY host_id
HAVING SUM(is_short) + SUM(is_over) > 0
ORDER BY host_id← Airbnb: Hosts With Three Cancellations Inside 30 Days · Airbnb: Quote Stays From Nightly Prices and Discounts →