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
| Column | Type |
|---|---|
| listing_id | int |
| city | varchar |
| title | varchar |
Primary key: listing_id.
One row per listing.
Table: WishlistSave
| Column | Type |
|---|---|
| save_id | int |
| guest_id | int |
| wishlist_name | varchar |
| listing_id | int |
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_id | city | title |
|---|---|---|
| 1 | Lisbon | Sunny loft near the old town |
| 2 | Lisbon | Canal-side apartment |
| 3 | Kyoto | Machiya townhouse |
| 4 | Kyoto | Minimalist micro-studio |
| 5 | Kyoto | Garden cottage |
| 6 | Goa | Beach hut with hammock |
WishlistSave
| save_id | guest_id | wishlist_name | listing_id |
|---|---|---|---|
| 1 | 100 | Summer | 1 |
| 2 | 100 | Someday | 1 |
| 3 | 101 | Summer | 1 |
| 4 | 102 | Summer | 2 |
| 5 | 100 | Japan | 3 |
| 6 | 103 | Japan | 3 |
| 7 | 104 | Kyoto trip | 4 |
| 8 | 101 | Kyoto trip | 4 |
| 9 | 102 | Someday | 5 |
Output
| city | listing_id | savers |
|---|---|---|
| Kyoto | 3 | 2 |
| Kyoto | 4 | 2 |
| Lisbon | 1 | 2 |
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 = 1Another 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 →