Airbnb: Most-Wishlisted Listing in Each City

Medium
559.

Airbnb: Most-Wishlisted Listing in Each City

Guests save homes they like to wishlists, and a guest can save the same home to more than one wishlist ("Summer", "Someday"). The merchandising team wants each city's most-wanted home for a destination page.

A listing's popularity is the number of distinct guests who saved it. For every city, return the listing (or listings, if tied) with the most savers: city, listing_id and savers. Listings nobody saved are never returned, so a city with no saves has no row. Return the rows 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: Listing

ColumnType
listing_idint
cityvarchar
titlevarchar

Primary key: listing_id.

One row per listing.

Table: WishlistSave

ColumnType
save_idint
guest_idint
wishlist_namevarchar
listing_idint

Primary key: save_id.

One row per listing saved to one of a guest's wishlists. listing_id always names a listing.

Example

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

Listing

listing_idcitytitle
1LisbonSunny loft near the old town
2LisbonCanal-side apartment
3KyotoMachiya townhouse
4KyotoMinimalist micro-studio
5KyotoGarden cottage
6GoaBeach hut with hammock

WishlistSave

save_idguest_idwishlist_namelisting_id
1100Summer1
2100Someday1
3101Summer1
4102Summer2
5100Japan3
6103Japan3
7104Kyoto trip4
8101Kyoto trip4
9102Someday5

Output

citylisting_idsavers
Kyoto32
Kyoto42
Lisbon12
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: Most-Wishlisted Listing in Each City — SQL Problem

Guests save homes they like to wishlists, and a guest can save the same home to more than one wishlist ("Summer", "Someday").

  • Difficulty: Medium
  • Topics: Window Functions, Aggregation, Joins
  • Dialect: MySQL
  • Problem: #559

Problem statement

Guests save homes they like to wishlists, and a guest can save the same home to more than one wishlist ("Summer", "Someday"). The merchandising team wants each city's most-wanted home for a destination page.

A listing's popularity is the number of distinct guests who saved it. For every city, return the listing (or listings, if tied) with the most savers: city, listing_id and savers. Listings nobody saved are never returned, so a city with no saves has no row. Return the rows 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: Listing

ColumnType
listing_idint
cityvarchar
titlevarchar

Primary key: listing_id.

One row per listing.

Table: WishlistSave

ColumnType
save_idint
guest_idint
wishlist_namevarchar
listing_idint

Primary key: save_id.

One row per listing saved to one of a guest's wishlists. listing_id always names a listing.

Examples

Example 1

Listing

listing_idcitytitle
1LisbonSunny loft near the old town
2LisbonCanal-side apartment
3KyotoMachiya townhouse
4KyotoMinimalist micro-studio
5KyotoGarden cottage
6GoaBeach hut with hammock

WishlistSave

save_idguest_idwishlist_namelisting_id
1100Summer1
2100Someday1
3101Summer1
4102Summer2
5100Japan3
6103Japan3
7104Kyoto trip4
8101Kyoto trip4
9102Someday5

Output

citylisting_idsavers
Kyoto32
Kyoto42
Lisbon12

How to solve Airbnb: Most-Wishlisted Listing in Each City

Two steps: measure each listing, then pick the best per city. The measure is COUNT(DISTINCT guest_id) over the listing's saves — COUNT(*) would count guest 100's two saves of listing 1 twice and could crown the wrong home. Joining Listing brings in the city, and the inner join means a listing with no saves never gets a row.

The pick is a top-per-group with ties. RANK() OVER (PARTITION BY city ORDER BY savers DESC) gives every listing its position inside its city, and equal counts share a position, so pos = 1 returns all tied leaders, as asked. ROW_NUMBER() would keep one of them, and which one would depend on storage order.

The alternatives drop the window: one keeps the listings whose count equals the city's maximum (a correlated MAX), the other keeps those for which no listing in the city has more savers (NOT EXISTS). All three agree on ties. The window version reads the counts once; the correlated ones may rescan them per row, which is fine at this size.

Reference solution (MySQL)

WITH counts AS (
  SELECT l.city, l.listing_id, COUNT(DISTINCT w.guest_id) AS savers
  FROM Listing l
  JOIN WishlistSave w ON w.listing_id = l.listing_id
  GROUP BY l.city, l.listing_id
), ranked AS (
  SELECT city, listing_id, savers,
         RANK() OVER (PARTITION BY city ORDER BY savers DESC) AS pos
  FROM counts
)
SELECT city, listing_id, savers FROM ranked WHERE pos = 1

Another way

WITH counts AS (
  SELECT l.city, l.listing_id, COUNT(DISTINCT w.guest_id) AS savers
  FROM Listing l JOIN WishlistSave w ON w.listing_id = l.listing_id
  GROUP BY l.city, l.listing_id
)
SELECT c.city, c.listing_id, c.savers FROM counts c
WHERE c.savers = (SELECT MAX(c2.savers) FROM counts c2 WHERE c2.city = c.city)

Another way

WITH counts AS (
  SELECT l.city, l.listing_id, COUNT(DISTINCT w.guest_id) AS savers
  FROM Listing l JOIN WishlistSave w ON w.listing_id = l.listing_id
  GROUP BY l.city, l.listing_id
)
SELECT c.city, c.listing_id, c.savers FROM counts c
WHERE NOT EXISTS (SELECT 1 FROM counts c2 WHERE c2.city = c.city AND c2.savers > c.savers)

← Airbnb: Search-to-Booking Funnel by Platform · Airbnb: Guests Who Came Back to the Same Host Within a Year →