Airbnb: Listings Whose Latest Review Scored Lower
A drop in a listing's latest review is an early warning — something may have changed at the home.
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #565
Problem statement
A drop in a listing's latest review is an early warning — something may have changed at the home. The trust team wants those listings flagged for a host check-in.
A listing's reviews are ordered by reviewed_on, and reviews on the same day by review_id; the latest is the last in that order and the previous the one just before it. Return every listing whose latest review's rating is strictly lower than the previous review's, with listing_id, previous_rating and latest_rating. Listings with only one review are never returned. 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: Review
| Column | Type |
|---|---|
| review_id | int |
| listing_id | int |
| reviewed_on | date |
| rating | int |
Primary key: review_id.
One row per guest review, 1 to 5 stars.
Examples
Example 1
Review
| review_id | listing_id | reviewed_on | rating |
|---|---|---|---|
| 1 | 10 | 2025-02-01 | 5 |
| 2 | 10 | 2025-02-20 | 3 |
| 3 | 11 | 2025-02-03 | 4 |
| 4 | 11 | 2025-02-11 | 5 |
| 5 | 12 | 2025-02-14 | 5 |
| 6 | 12 | 2025-02-14 | 4 |
| 7 | 13 | 2025-02-25 | 2 |
| 8 | 14 | 2025-03-01 | 4 |
| 9 | 14 | 2025-01-15 | 5 |
| 10 | 14 | 2025-02-01 | 3 |
Output
| listing_id | previous_rating | latest_rating |
|---|---|---|
| 10 | 5 | 3 |
| 12 | 5 | 4 |
How to solve Airbnb: Listings Whose Latest Review Scored Lower
Two reviews of the same listing have to be compared, and "latest" and "previous" are positions in an order — a job for window functions. LAG(rating) OVER (PARTITION BY listing_id ORDER BY reviewed_on, review_id) attaches to every review the rating of the review before it, and ROW_NUMBER() over the reverse order marks the latest review with from_end = 1. Keeping that row and testing rating < previous_rating answers the question.
The tie-break carries real weight. Two reviews can share a date, and without review_id in both windows their order — and therefore which one is "latest" — would be up to the engine. A listing with a single review has previous_rating NULL, and rating < NULL is unknown, so it is excluded without a special case. An equal rating is not a drop.
The alternative numbers the reviews from the end and self-joins position 1 to position 2 of the same listing. It avoids LAG but reads the numbered rows twice; the LAG version is a single pass after one sort per listing.
Reference solution (MySQL)
WITH ordered_reviews AS (
SELECT listing_id, rating,
LAG(rating) OVER (PARTITION BY listing_id ORDER BY reviewed_on, review_id) AS previous_rating,
ROW_NUMBER() OVER (PARTITION BY listing_id ORDER BY reviewed_on DESC, review_id DESC) AS from_end
FROM Review
)
SELECT listing_id, previous_rating, rating AS latest_rating
FROM ordered_reviews
WHERE from_end = 1 AND rating < previous_ratingAnother way
WITH r AS (
SELECT listing_id, rating,
ROW_NUMBER() OVER (PARTITION BY listing_id ORDER BY reviewed_on DESC, review_id DESC) AS from_end
FROM Review
)
SELECT a.listing_id, b.rating AS previous_rating, a.rating AS latest_rating
FROM r a JOIN r b ON b.listing_id = a.listing_id AND b.from_end = 2
WHERE a.from_end = 1 AND a.rating < b.rating← Airbnb: Hosts Whose Every Active Listing Is Instant Book · Airbnb: Longest Open Stretch on Each Listing's Calendar →