Airbnb: Reconcile Host Payouts Against Earnings

Hard
570.

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

ColumnTypeValues
reservation_idint
host_idint
statusenumupcoming, completed, cancelled
host_earningsint

Primary key: reservation_id.

host_earnings is what the host is owed for the stay, in US dollars.

Table: Payout

ColumnTypeValues
payout_idint
reservation_idint
amountint
statusenumsent, pending, failed

Primary key: payout_id.

One transfer towards a reservation; reservation_id always names a reservation.

Example

The rows must come back in the order shown.

Reservation

reservation_idhost_idstatushost_earnings
110completed500
210completed300
310cancelled200
420completed400
520upcoming250
630completed350
730cancelled150

Payout

payout_idreservation_idamountstatus
11300sent
21200sent
32300failed
42100sent
53200sent
64400sent
75250pending
86350pending
97150failed

Output

host_idshort_reservationsshort_amountoverpaid_reservationsoverpaid_amount
1012001200
30135000
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: 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

ColumnType
reservation_idint
host_idint
statusenum(upcoming, completed, cancelled)
host_earningsint

Primary key: reservation_id.

host_earnings is what the host is owed for the stay, in US dollars.

Table: Payout

ColumnType
payout_idint
reservation_idint
amountint
statusenum(sent, pending, failed)

Primary key: payout_id.

One transfer towards a reservation; reservation_id always names a reservation.

Examples

Example 1

Reservation

reservation_idhost_idstatushost_earnings
110completed500
210completed300
310cancelled200
420completed400
520upcoming250
630completed350
730cancelled150

Payout

payout_idreservation_idamountstatus
11300sent
21200sent
32300failed
42100sent
53200sent
64400sent
75250pending
86350pending
97150failed

Output

host_idshort_reservationsshort_amountoverpaid_reservationsoverpaid_amount
1012001200
30135000

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_id

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