Airbnb: Hosts Whose Every Active Listing Is Instant Book

Medium
564.

Airbnb: Hosts Whose Every Active Listing Is Instant Book

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

ColumnType
host_idint
host_namevarchar

Primary key: host_id.

One row per host.

Table: Listing

ColumnTypeValues
listing_idint
host_idint
instant_bookbool
statusenumactive, snoozed, delisted

Primary key: listing_id.

host_id always names a host.

Example

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

Host

host_idhost_name
1Priya
2David
3Ishaan
4Olivia

Listing

listing_idhost_idinstant_bookstatus
1011active
1111active
1210delisted
1321active
1420active
1531active
1631snoozed
1741active
1841active
1941active

Output

host_idhost_nameactive_listings
1Priya2
4Olivia3
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 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

ColumnType
host_idint
host_namevarchar

Primary key: host_id.

One row per host.

Table: Listing

ColumnType
listing_idint
host_idint
instant_bookbool
statusenum(active, snoozed, delisted)

Primary key: listing_id.

host_id always names a host.

Examples

Example 1

Host

host_idhost_name
1Priya
2David
3Ishaan
4Olivia

Listing

listing_idhost_idinstant_bookstatus
1011active
1111active
1210delisted
1321active
1420active
1531active
1631snoozed
1741active
1841active
1941active

Output

host_idhost_nameactive_listings
1Priya2
4Olivia3

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) = 1

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