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
| Column | Type |
|---|---|
| listing_id | int |
| title | varchar |
| amenities | varchar |
Primary key: listing_id.
amenities holds lower-case codes separated by commas, each code at most once, or NULL.
Examples
Example 1
Listing
| listing_id | title | amenities |
|---|---|---|
| 1 | Garden cottage | wifi,kitchen,washer |
| 2 | Beach hut with hammock | kitchen,parking |
| 3 | Minimalist micro-studio | kitchenette,heating |
| 4 | Converted barn | pocket_wifi,kitchen |
| 5 | Cliffside studio | NULL |
| 6 | Machiya townhouse | kitchen |
| 7 | Canal-side apartment | parking,wifi |
Output
| listing_id | title |
|---|---|
| 2 | Beach hut with hammock |
| 4 | Converted barn |
| 6 | Machiya 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_idAnother way
SELECT listing_id, title FROM Listing WHERE amenities REGEXP '(^|,)kitchen(,|$)' AND NOT amenities REGEXP '(^|,)wifi(,|$)' ORDER BY listing_idAnother 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 →