Airbnb: Host Payout for Each Completed Stay — SQL Problem
A finance analyst at Airbnb is checking the payout figures shown to hosts.
- Difficulty: Easy
- Topics: Basics, Conditional Logic
- Dialect: MySQL
- Problem: #554
Problem statement
A finance analyst at Airbnb is checking the payout figures shown to hosts. In this simplified model a host's gross for a stay is nights × nightly_price plus the cleaning fee (a NULL cleaning_fee means none, so 0), and the host receives the gross minus a 3% host service fee.
For every reservation with status completed, return reservation_id, gross and payout (gross × 0.97, rounded to 2 decimal places). Return the rows in any order.
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 |
| listing_id | int |
| nights | int |
| nightly_price | int |
| cleaning_fee | int |
| status | enum(upcoming, completed, cancelled) |
Primary key: reservation_id.
Amounts in US dollars. cleaning_fee is NULL when the listing charges none.
Examples
Example 1
Reservation
| reservation_id | listing_id | nights | nightly_price | cleaning_fee | status |
|---|---|---|---|---|---|
| 1 | 10 | 3 | 120 | 45 | completed |
| 2 | 10 | 2 | 120 | 45 | cancelled |
| 3 | 11 | 5 | 85 | NULL | completed |
| 4 | 12 | 1 | 210 | 60 | upcoming |
| 5 | 12 | 7 | 199 | 60 | completed |
Output
| reservation_id | gross | payout |
|---|---|---|
| 1 | 405 | 392.85 |
| 3 | 425 | 412.25 |
| 5 | 1453 | 1409.41 |
How to solve Airbnb: Host Payout for Each Completed Stay
Each row is computed on its own: the gross is nights * nightly_price plus the cleaning fee, and the payout takes 3% off it. The filter keeps only completed stays — an upcoming stay has not paid out yet and a cancelled one is not a stay.
The NULL fee is the trap. Arithmetic with NULL gives NULL, so a listing without a cleaning fee would show no gross and no payout at all. COALESCE(cleaning_fee, 0) (or IFNULL) substitutes 0 before adding.
MySQL cannot reuse a select alias inside the same SELECT, so the solution repeats the gross expression in the payout. The alternative computes gross once in a derived table and takes 3% off it in the outer query, which is easier to read when the expression is long. Multiplying a whole dollar figure by 0.97 gives at most two decimals, so the rounding never sits on a half cent. One scan of the table.
Reference solution (MySQL)
SELECT reservation_id,
nights * nightly_price + COALESCE(cleaning_fee, 0) AS gross,
ROUND((nights * nightly_price + COALESCE(cleaning_fee, 0)) * 0.97, 2) AS payout
FROM Reservation
WHERE status = 'completed'Another way
SELECT reservation_id, gross, ROUND(gross - gross * 3 / 100, 2) AS payout
FROM (SELECT reservation_id, status, nights * nightly_price + IFNULL(cleaning_fee, 0) AS gross FROM Reservation) t
WHERE status = 'completed'← Airbnb: Friday and Saturday Check-Ins per City · Airbnb: Host Display Names With a Last Initial →