Airbnb: Listings Whose Latest Review Scored Lower

Medium
565.

Airbnb: Listings Whose Latest Review Scored Lower

MediumWindow Functions

MySQL

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

ColumnType
review_idint
listing_idint
reviewed_ondate
ratingint

Primary key: review_id.

One row per guest review, 1 to 5 stars.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Review

review_idlisting_idreviewed_onrating
1102025-02-015
2102025-02-203
3112025-02-034
4112025-02-115
5122025-02-145
6122025-02-144
7132025-02-252
8142025-03-014
9142025-01-155
10142025-02-013

Output

listing_idprevious_ratinglatest_rating
1053
1254
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
review_idint
listing_idint
reviewed_ondate
ratingint

Primary key: review_id.

One row per guest review, 1 to 5 stars.

Examples

Example 1

Review

review_idlisting_idreviewed_onrating
1102025-02-015
2102025-02-203
3112025-02-034
4112025-02-115
5122025-02-145
6122025-02-144
7132025-02-252
8142025-03-014
9142025-01-155
10142025-02-013

Output

listing_idprevious_ratinglatest_rating
1053
1254

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_rating

Another 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 →