Airbnb: Hosts With No Active Listing — SQL Joins Problem
The host success team at Airbnb wants to call hosts who have nothing bookable right now, to help them get a home back into search.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #549
Problem statement
The host success team at Airbnb wants to call hosts who have nothing bookable right now, to help them get a home back into search.
Return every host who has no listing with status active — that includes hosts whose listings are all snoozed or delisted, and hosts who never created a listing. Return the columns host_id and host_name, 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 |
| joined_on | date |
Primary key: host_id.
One row per host account.
Table: Listing
| Column | Type |
|---|---|
| listing_id | int |
| host_id | int |
| status | enum(active, snoozed, delisted) |
Primary key: listing_id.
host_id always names a row of Host; a host can have any number of listings.
Examples
Example 1
Host
| host_id | host_name | joined_on |
|---|---|---|
| 1 | Aarav | 2021-04-12 |
| 2 | Meera | 2022-07-01 |
| 3 | Kabir | 2023-01-19 |
| 4 | Sara | 2023-09-30 |
| 5 | Liam | 2024-02-14 |
Listing
| listing_id | host_id | status |
|---|---|---|
| 10 | 1 | active |
| 11 | 1 | snoozed |
| 12 | 2 | snoozed |
| 13 | 2 | delisted |
| 14 | 3 | active |
| 15 | 5 | delisted |
Output
| host_id | host_name |
|---|---|
| 2 | Meera |
| 4 | Sara |
| 5 | Liam |
How to solve Airbnb: Hosts With No Active Listing
This is an anti join: keep the hosts for whom no active listing exists. NOT EXISTS states it directly — for each host the subquery looks for an active listing with that host_id, and the host survives only when it finds none. Hosts with no listings at all pass trivially, because the subquery has nothing to find.
The LEFT JOIN version is the classic trap. The join must carry l.status = 'active' in its ON clause, so every host keeps one row whose listing columns are NULL when no active listing matched; WHERE l.listing_id IS NULL then keeps exactly those. Moving the status test into WHERE would throw away the NULL rows and return nobody.
NOT IN works here as well, because Listing.host_id is never NULL — a single NULL in a NOT IN list makes every comparison unknown and empties the answer, which is why NOT EXISTS is the safer habit. All three run as one pass with an index on Listing(host_id).
Reference solution (MySQL)
SELECT h.host_id, h.host_name
FROM Host h
WHERE NOT EXISTS (
SELECT 1 FROM Listing l
WHERE l.host_id = h.host_id AND l.status = 'active'
)Another way
SELECT h.host_id, h.host_name FROM Host h LEFT JOIN Listing l ON l.host_id = h.host_id AND l.status = 'active' WHERE l.listing_id IS NULLAnother way
SELECT host_id, host_name FROM Host WHERE host_id NOT IN (SELECT host_id FROM Listing WHERE status = 'active')← Airbnb: Instant Book Listings for a Last-Minute Weekend · Airbnb: Average Cleaning Fee by Property Type →