Airbnb: Quote Stays From Nightly Prices and Discounts
When a guest picks dates, Airbnb quotes the stay from the host's price for each night plus any length-of-stay discount.
- Difficulty: Hard
- Topics: Joins, Aggregation, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #571
Problem statement
When a guest picks dates, Airbnb quotes the stay from the host's price for each night plus any length-of-stay discount. A pricing engineer is checking quotes against the calendar.
A request covers the nights from check_in up to the night before check_out; every one of those nights has a row in NightlyPrice. The nightly_total is the sum of their prices. The discount is a percentage of nightly_total: the listing's monthly_discount_pct if the stay is 28 nights or more and that column is not NULL; otherwise its weekly_discount_pct if the stay is 7 nights or more and that column is not NULL; otherwise none. The total is nightly_total − discount + cleaning_fee (NULL fee = 0). Return request_id, nights, nightly_total, discount and total (both rounded to 2 decimal places) for every request, ordered by request_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: Listing
| Column | Type |
|---|---|
| listing_id | int |
| cleaning_fee | int |
| weekly_discount_pct | int |
| monthly_discount_pct | int |
Primary key: listing_id.
Discounts are whole percentages; NULL means the host offers none. cleaning_fee is NULL when none is charged.
Table: NightlyPrice
| Column | Type |
|---|---|
| listing_id | int |
| night | date |
| price | int |
Primary key: listing_id, night.
The host's price for one night, in US dollars.
Table: StayRequest
| Column | Type |
|---|---|
| request_id | int |
| listing_id | int |
| check_in | date |
| check_out | date |
Primary key: request_id.
Dates a guest asked to book; check_out is after check_in.
Examples
Example 1
Listing
| listing_id | cleaning_fee | weekly_discount_pct | monthly_discount_pct |
|---|---|---|---|
| 1 | 40 | 10 | 25 |
| 2 | NULL | NULL | 30 |
NightlyPrice
| listing_id | night | price |
|---|---|---|
| 1 | 2025-02-01 | 100 |
| 1 | 2025-02-02 | 100 |
| 1 | 2025-02-03 | 100 |
| 1 | 2025-02-04 | 100 |
| 1 | 2025-02-05 | 100 |
| 1 | 2025-02-06 | 100 |
| 1 | 2025-02-07 | 140 |
| 1 | 2025-02-08 | 140 |
| 2 | 2025-02-01 | 90 |
| 2 | 2025-02-02 | 90 |
| 2 | 2025-02-03 | 90 |
| 2 | 2025-02-04 | 90 |
| 2 | 2025-02-05 | 90 |
| 2 | 2025-02-06 | 90 |
| 2 | 2025-02-07 | 110 |
| 2 | 2025-02-08 | 110 |
StayRequest
| request_id | listing_id | check_in | check_out |
|---|---|---|---|
| 1 | 1 | 2025-02-01 | 2025-02-08 |
| 2 | 1 | 2025-02-06 | 2025-02-08 |
| 3 | 2 | 2025-02-01 | 2025-02-09 |
| 4 | 2 | 2025-02-07 | 2025-02-08 |
Output
| request_id | nights | nightly_total | discount | total |
|---|---|---|---|---|
| 1 | 7 | 740 | 74 | 706 |
| 2 | 2 | 240 | 0 | 280 |
| 3 | 8 | 760 | 0 | 760 |
| 4 | 1 | 110 | 0 | 110 |
How to solve Airbnb: Quote Stays From Nightly Prices and Discounts
A quote has two parts: the nightly total, which comes from a range join, and the discount, which is a rule on the stay length. The range join matches each request to every NightlyPrice row of its listing with night >= check_in AND night < check_out — the check-out morning is not a night, so the bound is strict. Grouping by request gives COUNT(*) nights and SUM(price), and since every night has a price row the count equals DATEDIFF(check_out, check_in).
The discount rate is a CASE evaluated top-down: monthly first (28 nights or more and a monthly rate exists), then weekly (7 or more and a weekly rate exists), else 0. The order of the branches is the rule — a 30-night stay at a listing with only a weekly discount falls through the first branch to the second, which a naive IF(nights >= 28, monthly, weekly) would get wrong by returning NULL. The cleaning fee joins as COALESCE(cleaning_fee, 0).
Whole-dollar totals times whole percentages divided by 100 have at most two decimals, so the rounding is exact. The alternative sums the nights with correlated subqueries and nests IFs for the rule. The join version touches each price row once per covering request.
Reference solution (MySQL)
WITH nightly AS (
SELECT q.request_id, q.listing_id, COUNT(*) AS nights, SUM(p.price) AS nightly_total
FROM StayRequest q
JOIN NightlyPrice p ON p.listing_id = q.listing_id AND p.night >= q.check_in AND p.night < q.check_out
GROUP BY q.request_id, q.listing_id
), priced AS (
SELECT n.request_id, n.nights, n.nightly_total, l.cleaning_fee,
CASE WHEN n.nights >= 28 AND l.monthly_discount_pct IS NOT NULL THEN l.monthly_discount_pct
WHEN n.nights >= 7 AND l.weekly_discount_pct IS NOT NULL THEN l.weekly_discount_pct
ELSE 0 END AS pct
FROM nightly n
JOIN Listing l ON l.listing_id = n.listing_id
)
SELECT request_id, nights, nightly_total,
ROUND(nightly_total * pct / 100, 2) AS discount,
ROUND(nightly_total - nightly_total * pct / 100 + COALESCE(cleaning_fee, 0), 2) AS total
FROM priced
ORDER BY request_idAnother way
SELECT request_id, nights, nightly_total, ROUND(disc, 2) AS discount, ROUND(nightly_total - disc + fee, 2) AS total
FROM (
SELECT q.request_id,
DATEDIFF(q.check_out, q.check_in) AS nights,
(SELECT SUM(p.price) FROM NightlyPrice p WHERE p.listing_id = q.listing_id AND p.night BETWEEN q.check_in AND DATE_SUB(q.check_out, INTERVAL 1 DAY)) AS nightly_total,
(SELECT SUM(p.price) FROM NightlyPrice p WHERE p.listing_id = q.listing_id AND p.night BETWEEN q.check_in AND DATE_SUB(q.check_out, INTERVAL 1 DAY))
* IF(DATEDIFF(q.check_out, q.check_in) >= 28 AND l.monthly_discount_pct IS NOT NULL, l.monthly_discount_pct,
IF(DATEDIFF(q.check_out, q.check_in) >= 7, IFNULL(l.weekly_discount_pct, 0), 0)) / 100 AS disc,
IFNULL(l.cleaning_fee, 0) AS fee
FROM StayRequest q JOIN Listing l ON l.listing_id = q.listing_id
) t
ORDER BY request_id← Airbnb: Reconcile Host Payouts Against Earnings · Airbnb: Attribute Each Booking to Its Last Search →