Airbnb's marketplace analytics team wants to compare listing performance during the first quarter of 2025. For every listing with at least two confirmed bookings that started during the first quarter of 2025, return the listing ID, listing title, host name, neighborhood, number of confirmed bookings, total booking revenue, and average guest rating for those bookings. Only bookings with status `confirmed` and check-in dates from January 1 through March 31, 2025 should be included. Missing ratings must not affect the average, but listings without any ratings should still appear with NULL as the average rating. A zero booking price is valid. Round total revenue to two decimal places and average guest rating to two decimal places. Sort by total booking revenue descending, then listing ID descending. ### Table Schema | Table | Column | Type | |---|---|---| | hosts | host_id | integer | | hosts | host_name | varchar(100) | | neighborhoods | neighborhood_id | integer | | neighborhoods | neighborhood_name | varchar(100) | | listings | listing_id | integer | | listings | listing_title | varchar(150) | | listings | host_id | integer | | listings | neighborhood_id | integer | | bookings | booking_id | integer | | bookings | listing_id | integer | | bookings | check_in | date | | bookings | status | varchar(20) | | bookings | total_price | decimal(10,2) | | booking_reviews | review_id | integer | | booking_reviews | booking_id | integer | | booking_reviews | rating | decimal(2,1) | ### Example Input: listings | listing_id | listing_title | host_id | neighborhood_id | |---:|---|---:|---:| | 101 | Harbor Loft | 1 | 10 | | 102 | Garden Studio | 2 | 20 | | 103 | City View Flat | 1 | 10 | ### Example Input: bookings | booking_id | listing_id | check_in | status | total_price | |---:|---:|---|---|---:| | 1001 | 101 | 2025-01-05 | confirmed | 400.00 | | 1002 | 101 | 2025-02-10 | confirmed | 600.00 | | 1003 | 101 | 2025-03-15 | cancelled | 500.00 | | 1004 | 102 | 2025-01-20 | confirmed | 300.00 | | 1005 | 102 | 2025-03-01 | confirmed | 500.00 | | 1006 | 103 | 2025-02-05 | confirmed | 200.00 | ### Example Input: booking_reviews | review_id | booking_id | rating | |---:|---:|---:| | 1 | 1001 | 4.5 | | 2 | 1002 | 5.0 | | 3 | 1004 | NULL | | 4 | 1005 | 3.5 | ### Example Output Explanation Harbor Loft has two confirmed first-quarter bookings totaling 1000.00, with an average guest rating of 4.75. Garden Studio also qualifies with two bookings totaling 800.00, but its only valid rating is 3.50 because the NULL rating is ignored. City View Flat has only one qualifying booking and is excluded.
DROP TABLE IF EXISTS booking_reviews; DROP TABLE IF EXISTS bookings; DROP TABLE IF EXISTS listings; DROP TABLE IF EXISTS neighborhoods; DROP TABLE IF EXISTS hosts; CREATE TABLE hosts ( host_id INT, host_name VARCHAR(100) ); CREATE TABLE neighborhoods ( neighborhood_id INT, neighborhood_name VARCHAR(100) ); CREATE TABLE listings ( listing_id INT, listing_title VARCHAR(150), host_id INT, neighborhood_id INT ); CREATE TABLE bookings ( booking_id INT, listing_id INT, check_in DATE, status VARCHAR(20), total_price DECIMAL(10,2) ); CREATE TABLE booking_reviews ( review_id INT, booking_id INT, rating DECIMAL(2,1) ); INSERT INTO hosts VALUES (1, 'Ava Morgan'), (2, 'Ben Carter'); INSERT INTO neighborhoods VALUES (10, 'Harbor District'), (20, 'Garden Quarter'); INSERT INTO listings VALUES (101, 'Harbor Loft', 1, 10), (102, 'Garden Studio', 2, 20), (103, 'City View Flat', 1, 10); INSERT INTO bookings VALUES (1001, 101, '2025-01-05', 'confirmed', 400.00), (1002, 101, '2025-02-10', 'confirmed', 600.00), (1003, 101, '2025-03-15', 'cancelled', 500.00), (1004, 102, '2025-01-20', 'confirmed', 300.00), (1005, 102, '2025-03-01', 'confirmed', 500.00), (1006, 103, '2025-02-05', 'confirmed', 200.00); INSERT INTO booking_reviews VALUES (1, 1001, 4.5), (2, 1002, 5.0), (3, 1004, NULL), (4, 1005, 3.5);