Airbnb: Hosts With No Active Listing

Easy
549.

Airbnb: Hosts With No Active Listing

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

ColumnType
host_idint
host_namevarchar
joined_ondate

Primary key: host_id.

One row per host account.

Table: Listing

ColumnTypeValues
listing_idint
host_idint
statusenumactive, snoozed, delisted

Primary key: listing_id.

host_id always names a row of Host; a host can have any number of listings.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Host

host_idhost_namejoined_on
1Aarav2021-04-12
2Meera2022-07-01
3Kabir2023-01-19
4Sara2023-09-30
5Liam2024-02-14

Listing

listing_idhost_idstatus
101active
111snoozed
122snoozed
132delisted
143active
155delisted

Output

host_idhost_name
2Meera
4Sara
5Liam
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: 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

ColumnType
host_idint
host_namevarchar
joined_ondate

Primary key: host_id.

One row per host account.

Table: Listing

ColumnType
listing_idint
host_idint
statusenum(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_idhost_namejoined_on
1Aarav2021-04-12
2Meera2022-07-01
3Kabir2023-01-19
4Sara2023-09-30
5Liam2024-02-14

Listing

listing_idhost_idstatus
101active
111snoozed
122snoozed
132delisted
143active
155delisted

Output

host_idhost_name
2Meera
4Sara
5Liam

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 NULL

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