Airbnb: Instant Book Listings for a Last-Minute Weekend
A growth marketer at Airbnb is building a "last-minute weekend" email: it may only feature homes a guest can book on the spot and leave without a heavy…
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #548
Problem statement
A growth marketer at Airbnb is building a "last-minute weekend" email: it may only feature homes a guest can book on the spot and leave without a heavy penalty.
Return every listing that is active, has Instant Book on (instant_book = 1), uses the flexible or moderate cancellation policy, and accepts a stay of two nights or fewer (min_nights at most 2). Return the columns listing_id, city and nightly_price, ordered by nightly_price ascending, then listing_id ascending.
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 |
| city | varchar |
| nightly_price | int |
| min_nights | int |
| instant_book | bool |
| cancellation_policy | enum(flexible, moderate, firm, strict) |
| status | enum(active, snoozed, delisted) |
Primary key: listing_id.
One row per listing. nightly_price is the base price in US dollars; min_nights is the shortest stay the host accepts; a snoozed listing is temporarily hidden from search.
Examples
Example 1
Listing
| listing_id | city | nightly_price | min_nights | instant_book | cancellation_policy | status |
|---|---|---|---|---|---|---|
| 1 | Lisbon | 120 | 2 | 1 | flexible | active |
| 2 | Lisbon | 95 | 3 | 1 | flexible | active |
| 3 | Goa | 60 | 1 | 1 | moderate | active |
| 4 | Goa | 60 | 1 | 0 | flexible | active |
| 5 | Kyoto | 140 | 1 | 1 | strict | active |
| 6 | Kyoto | 85 | 2 | 1 | moderate | snoozed |
| 7 | Bali | 60 | 2 | 1 | flexible | active |
Output
| listing_id | city | nightly_price |
|---|---|---|
| 3 | Goa | 60 |
| 7 | Bali | 60 |
| 1 | Lisbon | 120 |
How to solve Airbnb: Instant Book Listings for a Last-Minute Weekend
This is a filter on one table: each listing either belongs in the email or it does not. Four conditions must all be true, so they sit together in WHERE joined by AND — the listing is active (a snoozed or delisted home cannot be booked), Instant Book is on, the cancellation policy is one of the two lenient ones, and the minimum stay is at most two nights.
Two details decide the boundary rows. min_nights <= 2 keeps a listing whose minimum is exactly two, which a strict < would lose; and IN ('flexible', 'moderate') is shorter and safer than two ORed equalities, which would need brackets to sit correctly beside the ANDs.
The statement fixes the order, and several listings can share a price, so ORDER BY nightly_price, listing_id breaks the ties by id. The alternative spells the same sets the other way round — NOT IN ('firm', 'strict') and min_nights < 3 — which only works because the enum has exactly four values and the column is never NULL. The query is one scan of the table.
Reference solution (MySQL)
SELECT listing_id, city, nightly_price
FROM Listing
WHERE status = 'active'
AND instant_book = 1
AND cancellation_policy IN ('flexible', 'moderate')
AND min_nights <= 2
ORDER BY nightly_price, listing_idAnother way
SELECT listing_id, city, nightly_price FROM Listing WHERE status = 'active' AND instant_book <> 0 AND cancellation_policy NOT IN ('firm', 'strict') AND min_nights < 3 ORDER BY nightly_price ASC, listing_id ASC← Date Each Frequent Flyer Qualified for Gold · Airbnb: Hosts With No Active Listing →