Airbnb: Instant Book Listings for a Last-Minute Weekend

Easy
548.

Airbnb: Instant Book Listings for a Last-Minute Weekend

EasyBasics

MySQL

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

ColumnTypeValues
listing_idint
cityvarchar
nightly_priceint
min_nightsint
instant_bookbool
cancellation_policyenumflexible, moderate, firm, strict
statusenumactive, 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.

Example

The rows must come back in the order shown.

Listing

listing_idcitynightly_pricemin_nightsinstant_bookcancellation_policystatus
1Lisbon12021flexibleactive
2Lisbon9531flexibleactive
3Goa6011moderateactive
4Goa6010flexibleactive
5Kyoto14011strictactive
6Kyoto8521moderatesnoozed
7Bali6021flexibleactive

Output

listing_idcitynightly_price
3Goa60
7Bali60
1Lisbon120
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: 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

ColumnType
listing_idint
cityvarchar
nightly_priceint
min_nightsint
instant_bookbool
cancellation_policyenum(flexible, moderate, firm, strict)
statusenum(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_idcitynightly_pricemin_nightsinstant_bookcancellation_policystatus
1Lisbon12021flexibleactive
2Lisbon9531flexibleactive
3Goa6011moderateactive
4Goa6010flexibleactive
5Kyoto14011strictactive
6Kyoto8521moderatesnoozed
7Bali6021flexibleactive

Output

listing_idcitynightly_price
3Goa60
7Bali60
1Lisbon120

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_id

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