Airbnb: Quote Stays From Nightly Prices and Discounts

Hard
571.

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

ColumnType
listing_idint
cleaning_feeint
weekly_discount_pctint
monthly_discount_pctint

Primary key: listing_id.

Discounts are whole percentages; NULL means the host offers none. cleaning_fee is NULL when none is charged.

Table: NightlyPrice

ColumnType
listing_idint
nightdate
priceint

Primary key (together): listing_id, night.

The host's price for one night, in US dollars.

Table: StayRequest

ColumnType
request_idint
listing_idint
check_indate
check_outdate

Primary key: request_id.

Dates a guest asked to book; check_out is after check_in.

Example

The rows must come back in the order shown.

Listing

listing_idcleaning_feeweekly_discount_pctmonthly_discount_pct
1401025
2NULLNULL30

NightlyPrice

listing_idnightprice
12025-02-01100
12025-02-02100
12025-02-03100
12025-02-04100
12025-02-05100
12025-02-06100
12025-02-07140
12025-02-08140
22025-02-0190
22025-02-0290
6 more rows

StayRequest

request_idlisting_idcheck_incheck_out
112025-02-012025-02-08
212025-02-062025-02-08
322025-02-012025-02-09
422025-02-072025-02-08

Output

request_idnightsnightly_totaldiscounttotal
1774074706
222400280
387600760
411100110
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: 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

ColumnType
listing_idint
cleaning_feeint
weekly_discount_pctint
monthly_discount_pctint

Primary key: listing_id.

Discounts are whole percentages; NULL means the host offers none. cleaning_fee is NULL when none is charged.

Table: NightlyPrice

ColumnType
listing_idint
nightdate
priceint

Primary key: listing_id, night.

The host's price for one night, in US dollars.

Table: StayRequest

ColumnType
request_idint
listing_idint
check_indate
check_outdate

Primary key: request_id.

Dates a guest asked to book; check_out is after check_in.

Examples

Example 1

Listing

listing_idcleaning_feeweekly_discount_pctmonthly_discount_pct
1401025
2NULLNULL30

NightlyPrice

listing_idnightprice
12025-02-01100
12025-02-02100
12025-02-03100
12025-02-04100
12025-02-05100
12025-02-06100
12025-02-07140
12025-02-08140
22025-02-0190
22025-02-0290
22025-02-0390
22025-02-0490
22025-02-0590
22025-02-0690
22025-02-07110
22025-02-08110

StayRequest

request_idlisting_idcheck_incheck_out
112025-02-012025-02-08
212025-02-062025-02-08
322025-02-012025-02-09
422025-02-072025-02-08

Output

request_idnightsnightly_totaldiscounttotal
1774074706
222400280
387600760
411100110

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_id

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