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: 1. Received a search impression. 2. Received a guest inquiry. 3. Received a confirmed booking. A listing should count only once at each milestone, even if it generated multiple events. Events that occurred before publication should not count. Treat missing city values as `UNKNOWN`. Return the columns in this exact order: `city`, `published_listings`, `impression_listings`, `inquiry_listings`, `booked_listings`, `impression_rate`, `inquiry_rate`, and `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. ### Table Schema | Table | Column | Type | |---|---|---| | `listing_events` | `event_id` | integer | | `listing_events` | `listing_id` | integer | | `listing_events` | `host_id` | integer | | `listing_events` | `city` | varchar(50) | | `listing_events` | `event_name` | varchar(30) | | `listing_events` | `event_at` | timestamp | ### Example Input: `listing_events` | event_id | listing_id | host_id | city | event_name | event_at | |---:|---:|---:|---|---|---| | 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 | ### Example Output Explanation 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');