Airbnb: Hosts Whose Every Active Listing Is Instant Book
Instant Book lets a guest book without waiting for approval.
- Difficulty: Medium
- Topics: Aggregation, Subqueries
- Dialect: MySQL
- Problem: #564
Problem statement
Instant Book lets a guest book without waiting for approval. The host-tools team is choosing multi-listing hosts for a beta of a new calendar feature and wants hosts who have gone all in on Instant Book.
Return every host with at least two active listings where every active listing has instant_book = 1. Snoozed and delisted listings are ignored entirely, whatever their setting. Return host_id, host_name and active_listings, in any order.
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: Host
| Column | Type |
|---|---|
| host_id | int |
| host_name | varchar |
Primary key: host_id.
One row per host.
Table: Listing
| Column | Type |
|---|---|
| listing_id | int |
| host_id | int |
| instant_book | bool |
| status | enum(active, snoozed, delisted) |
Primary key: listing_id.
host_id always names a host.
Examples
Example 1
Host
| host_id | host_name |
|---|---|
| 1 | Priya |
| 2 | David |
| 3 | Ishaan |
| 4 | Olivia |
Listing
| listing_id | host_id | instant_book | status |
|---|---|---|---|
| 10 | 1 | 1 | active |
| 11 | 1 | 1 | active |
| 12 | 1 | 0 | delisted |
| 13 | 2 | 1 | active |
| 14 | 2 | 0 | active |
| 15 | 3 | 1 | active |
| 16 | 3 | 1 | snoozed |
| 17 | 4 | 1 | active |
| 18 | 4 | 1 | active |
| 19 | 4 | 1 | active |
Output
| host_id | host_name | active_listings |
|---|---|---|
| 1 | Priya | 2 |
| 4 | Olivia | 3 |
How to solve Airbnb: Hosts Whose Every Active Listing Is Instant Book
"Every" questions are a kind of relational division, and the easiest way to answer one in SQL is to turn it into an aggregate. After keeping only active listings and grouping by host, "every active listing has Instant Book" becomes MIN(instant_book) = 1 — the minimum of 0/1 flags is 1 only when no 0 is present. Another spelling is SUM(instant_book) = COUNT(*). Both sit in HAVING with the size rule COUNT(*) >= 2.
Filtering in WHERE before grouping is what makes snoozed and delisted listings irrelevant: a delisted listing without Instant Book cannot spoil a host, and a snoozed one with it cannot make up the count of two. A host with no active listings never forms a group.
The other alternative states the rule literally: count the active listings with a correlated subquery, and require that no active listing with instant_book = 0 exists. Double negation ("no listing that is not…") is the textbook form of division; the grouped version reads the table once and is usually what a reviewer expects.
Reference solution (MySQL)
SELECT h.host_id, h.host_name, COUNT(*) AS active_listings
FROM Host h
JOIN Listing l ON l.host_id = h.host_id
WHERE l.status = 'active'
GROUP BY h.host_id, h.host_name
HAVING COUNT(*) >= 2 AND MIN(l.instant_book) = 1Another way
SELECT h.host_id, h.host_name,
(SELECT COUNT(*) FROM Listing l WHERE l.host_id = h.host_id AND l.status = 'active') AS active_listings
FROM Host h
WHERE (SELECT COUNT(*) FROM Listing l WHERE l.host_id = h.host_id AND l.status = 'active') >= 2
AND NOT EXISTS (SELECT 1 FROM Listing l WHERE l.host_id = h.host_id AND l.status = 'active' AND l.instant_book = 0)Another way
SELECT h.host_id, h.host_name, COUNT(*) AS active_listings
FROM Host h JOIN Listing l ON l.host_id = h.host_id AND l.status = 'active'
GROUP BY h.host_id, h.host_name
HAVING COUNT(*) >= 2 AND SUM(l.instant_book) = COUNT(*)← Airbnb: Listings With a Kitchen but No Wi-Fi · Airbnb: Listings Whose Latest Review Scored Lower →