Airbnb Airbnb Interview Question 05

Listings
with No
Bookings

Find listings that have never been booked.

Table Schema

Inspect
Table

interactive

Column	Type
listing_id	integer
booking_id	integer

Sample Data

Sample Input: listings

Sample Input: bookings

Expected Output

listing_id
103
104

SQL Editor

Waiting for query

listing_id
103
104

Hints

Hint 01: Identify the grouping level required by the output.

Hint 02: Aggregate with COUNT, SUM, AVG, or a window function as needed.

Hint 03: Filter after aggregation with HAVING or after ranking with an outer query.

Solution

Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.

SELECT l.listing_id
FROM listings l
LEFT JOIN bookings b
  ON l.listing_id = b.listing_id
WHERE b.booking_id IS NULL;

Explanation

Read the expected output columns to determine the final grain.

Aggregate or rank the input rows to calculate the requested metric.

Filter, sort, and alias the final columns to match the output.