Airbnb's operations team wants to know whether listings were properly prepared between back-to-back guest stays. For every confirmed reservation that checked out during March 2025 and was followed by another confirmed reservation at the same listing within 48 hours, find the most recent completed cleaning performed after the first checkout and no later than the next guest's check-in. Return the reservation ID, listing ID, checkout time, next check-in time, cleaning time, and turnover status. Mark the turnover as `Ready` when a qualifying completed cleaning exists and `Missed` otherwise. Cancelled reservations must not be considered when identifying the next reservation. If multiple completed cleanings have the same timestamp, use the cleaning with the greater cleaning ID. Sort the results by reservation ID descending. ### Table Schema | Table | Column | Type | |---|---|---| | listings | listing_id | integer | | listings | listing_name | varchar(100) | | reservations | reservation_id | integer | | reservations | listing_id | integer | | reservations | check_in | timestamp | | reservations | check_out | timestamp | | reservations | status | varchar(20) | | cleaning_events | cleaning_id | integer | | cleaning_events | listing_id | integer | | cleaning_events | cleaned_at | timestamp | | cleaning_events | status | varchar(20) | ### Example Input: listings | listing_id | listing_name | |---:|---| | 101 | Harbor Loft | | 102 | Garden Studio | ### Example Input: reservations | reservation_id | listing_id | check_in | check_out | status | |---:|---:|---|---|---| | 1 | 101 | 2025-03-01 15:00:00 | 2025-03-04 11:00:00 | confirmed | | 2 | 101 | 2025-03-05 14:00:00 | 2025-03-08 11:00:00 | confirmed | | 3 | 102 | 2025-03-02 15:00:00 | 2025-03-05 11:00:00 | confirmed | | 4 | 102 | 2025-03-05 11:00:00 | 2025-03-08 11:00:00 | confirmed | ### Example Input: cleaning_events | cleaning_id | listing_id | cleaned_at | status | |---:|---:|---|---| | 11 | 101 | 2025-03-04 13:00:00 | completed | | 12 | 101 | 2025-03-04 12:00:00 | failed | | 13 | 102 | 2025-03-05 12:00:00 | completed | ### Example Output Explanation Reservation 1 is followed by reservation 2 five hours later, and a completed cleaning occurred between checkout and the next check-in, so it is marked `Ready`. Reservation 3 is followed immediately by reservation 4 and has a completed cleaning after checkout, so it is also `Ready`. Only the first reservation in each turnover pair is returned.
DROP TABLE IF EXISTS cleaning_events; DROP TABLE IF EXISTS reservations; DROP TABLE IF EXISTS listings; CREATE TABLE listings ( listing_id INT, listing_name VARCHAR(100) ); CREATE TABLE reservations ( reservation_id INT, listing_id INT, check_in TIMESTAMP, check_out TIMESTAMP, status VARCHAR(20) ); CREATE TABLE cleaning_events ( cleaning_id INT, listing_id INT, cleaned_at TIMESTAMP, status VARCHAR(20) ); INSERT INTO listings VALUES (101, 'Harbor Loft'), (102, 'Garden Studio'); INSERT INTO reservations VALUES (1, 101, '2025-03-01 15:00:00', '2025-03-04 11:00:00', 'confirmed'), (2, 101, '2025-03-05 14:00:00', '2025-03-08 11:00:00', 'confirmed'), (3, 102, '2025-03-02 15:00:00', '2025-03-05 11:00:00', 'confirmed'), (4, 102, '2025-03-05 11:00:00', '2025-03-08 11:00:00', 'confirmed'); INSERT INTO cleaning_events VALUES (11, 101, '2025-03-04 13:00:00', 'completed'), (12, 101, '2025-03-04 12:00:00', 'failed'), (13, 102, '2025-03-05 12:00:00', 'completed');