Airbnb: Listings With a Kitchen but No Wi-Fi

Medium
563.

Airbnb: Listings With a Kitchen but No Wi-Fi

MediumStrings

MySQL

Long-stay guests filter for a kitchen and Wi-Fi together. The listings-quality team wants hosts who advertise a kitchen but no Wi-Fi, so they can be nudged to add it.

A listing's amenities is a comma-separated list of codes with no spaces, such as wifi,kitchen,washer. Return the listings whose list contains the code kitchen exactly and does not contain the code wifi exactly — kitchenette is not a kitchen, and pocket_wifi is not Wi-Fi. A listing with amenities NULL lists nothing and is never returned. Return listing_id and title, ordered by listing_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
titlevarchar
amenitiesvarchar

Primary key: listing_id.

amenities holds lower-case codes separated by commas, each code at most once, or NULL.

Example

The rows must come back in the order shown.

Listing

listing_idtitleamenities
1Garden cottagewifi,kitchen,washer
2Beach hut with hammockkitchen,parking
3Minimalist micro-studiokitchenette,heating
4Converted barnpocket_wifi,kitchen
5Cliffside studioNULL
6Machiya townhousekitchen
7Canal-side apartmentparking,wifi

Output

listing_idtitle
2Beach hut with hammock
4Converted barn
6Machiya townhouse
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: Listings With a Kitchen but No Wi-Fi — SQL Problem

Long-stay guests filter for a kitchen and Wi-Fi together. The listings-quality team wants hosts who advertise a kitchen but no Wi-Fi, so they can be nudged…

  • Difficulty: Medium
  • Topics: Strings
  • Dialect: MySQL
  • Problem: #563

Problem statement

Long-stay guests filter for a kitchen and Wi-Fi together. The listings-quality team wants hosts who advertise a kitchen but no Wi-Fi, so they can be nudged to add it.

A listing's amenities is a comma-separated list of codes with no spaces, such as wifi,kitchen,washer. Return the listings whose list contains the code kitchen exactly and does not contain the code wifi exactly — kitchenette is not a kitchen, and pocket_wifi is not Wi-Fi. A listing with amenities NULL lists nothing and is never returned. Return listing_id and title, ordered by listing_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
titlevarchar
amenitiesvarchar

Primary key: listing_id.

amenities holds lower-case codes separated by commas, each code at most once, or NULL.

Examples

Example 1

Listing

listing_idtitleamenities
1Garden cottagewifi,kitchen,washer
2Beach hut with hammockkitchen,parking
3Minimalist micro-studiokitchenette,heating
4Converted barnpocket_wifi,kitchen
5Cliffside studioNULL
6Machiya townhousekitchen
7Canal-side apartmentparking,wifi

Output

listing_idtitle
2Beach hut with hammock
4Converted barn
6Machiya townhouse

How to solve Airbnb: Listings With a Kitchen but No Wi-Fi

Searching a delimited list for a whole item is a string problem: a plain LIKE '%kitchen%' also finds kitchenette, and '%wifi%' finds pocket_wifi. The standard trick is to put a comma at both ends — CONCAT(',', amenities, ',') turns kitchen,parking into ,kitchen,parking, — after which every code, including the first and the last, sits between two commas, and LIKE '%,kitchen,%' matches only the whole code.

The listing must contain ,kitchen, and must not contain ,wifi,. NULL amenities need no extra test: CONCAT with a NULL argument is NULL, NULL LIKE … is unknown and NULL NOT LIKE … is unknown too, so the row fails WHERE either way — exactly the "never returned" rule.

The alternatives use the same idea differently: a regular expression anchored on (^|,) and (,|$) needs no padding, and LOCATE finds the padded code's position (0 for absent). In a real schema the amenities would be a separate table with one row per code, which turns this into an indexed semi join plus anti join.

Reference solution (MySQL)

SELECT listing_id, title
FROM Listing
WHERE CONCAT(',', amenities, ',') LIKE '%,kitchen,%'
  AND CONCAT(',', amenities, ',') NOT LIKE '%,wifi,%'
ORDER BY listing_id

Another way

SELECT listing_id, title FROM Listing WHERE amenities REGEXP '(^|,)kitchen(,|$)' AND NOT amenities REGEXP '(^|,)wifi(,|$)' ORDER BY listing_id

Another way

SELECT listing_id, title FROM Listing WHERE LOCATE(',kitchen,', CONCAT(',', amenities, ',')) > 0 AND LOCATE(',wifi,', CONCAT(',', amenities, ',')) = 0 ORDER BY listing_id

← Airbnb: Guest Cancellation Rate by Cancellation Policy · Airbnb: Hosts Whose Every Active Listing Is Instant Book →