Company: Airbnb | Difficulty: medium
Airbnb's marketplace team wants to understand how newly published listings progress toward their first confirmed booking.
For listings published during the first quarter of 2025, group results by city and count how many listings reached each milestone after publication:
A listing should count only once at each milestone, even if it generated multiple events. Events that occurred before publication should not count. A listing's event history may include events recorded outside its most recent publication (for example, if it was previously published, unpublished, and republished) — only events occurring after the specific published event being evaluated should count toward a milestone. Treat missing city values as UNKNOWN.
published
UNKNOWN
Return the columns in this exact order: city, published_listings, impression_listings, inquiry_listings, booked_listings, impression_rate, inquiry_rate, and booking_rate.
city
published_listings
impression_listings
inquiry_listings
booked_listings
impression_rate
inquiry_rate
booking_rate
Calculate each rate using the number of published listings as the denominator and round rates to four decimal places. Sort by booking_rate descending, then city descending.
listing_events
event_id
listing_id
host_id
event_name
event_at
Paris has two published listings. Both received impressions, one received an inquiry, and one received a confirmed booking. Rome has one published listing that received an impression and inquiry but no booking. Therefore, Paris has a booking rate of 0.5000 and Rome has a booking rate of 0.0000.
DROP TABLE IF EXISTS listing_events; CREATE TABLE listing_events ( event_id INT, listing_id INT, host_id INT, city VARCHAR(50), event_name VARCHAR(30), event_at TIMESTAMP ); INSERT INTO listing_events VALUES (1,101,9001,'Paris','published','2025-01-02 09:00:00'), (2,101,9001,'Paris','search_impression','2025-01-03 10:00:00'), (3,101,9001,'Paris','inquiry','2025-01-04 11:00:00'), (4,101,9001,'Paris','booking_confirmed','2025-01-05 12:00:00'), (5,102,9002,'Paris','published','2025-01-06 09:00:00'), (6,102,9002,'Paris','search_impression','2025-01-07 10:00:00'), (7,103,9003,'Rome','published','2025-02-01 09:00:00'), (8,103,9003,'Rome','search_impression','2025-02-02 10:00:00'), (9,103,9003,'Rome','inquiry','2025-02-03 11:00:00');
← All SQL Challenges · Airbnb Questions · Learn SQL Free