Airbnb: Host Payout for Each Completed Stay

Easy
554.

Airbnb: Host Payout for Each Completed Stay

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

ColumnTypeValues
reservation_idint
listing_idint
nightsint
nightly_priceint
cleaning_feeint
statusenumupcoming, completed, cancelled

Primary key: reservation_id.

Amounts in US dollars. cleaning_fee is NULL when the listing charges none.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Reservation

reservation_idlisting_idnightsnightly_pricecleaning_feestatus
110312045completed
210212045cancelled
311585NULLcompleted
412121060upcoming
512719960completed

Output

reservation_idgrosspayout
1405392.85
3425412.25
514531409.41
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: 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

ColumnType
reservation_idint
listing_idint
nightsint
nightly_priceint
cleaning_feeint
statusenum(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_idlisting_idnightsnightly_pricecleaning_feestatus
110312045completed
210212045cancelled
311585NULLcompleted
412121060upcoming
512719960completed

Output

reservation_idgrosspayout
1405392.85
3425412.25
514531409.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 →