Airbnb: Reviews Where Cleanliness Trails the Overall Rating
Guests on Airbnb give an overall star rating plus category ratings such as cleanliness.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #551
Problem statement
Guests on Airbnb give an overall star rating plus category ratings such as cleanliness. The quality team wants reviews where the stay was liked overall but the cleaning clearly was not, to pass on to the hosts' cleaning partners.
Return every review whose cleanliness rating is at least 2 stars below its overall rating. A review whose guest skipped the cleanliness question (cleanliness NULL) is never returned. Return review_id, listing_id and gap (overall minus cleanliness), ordered by gap descending, then review_id ascending.
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 |
| overall | int |
| cleanliness | int |
| submitted_on | date |
Primary key: review_id.
Ratings are whole stars from 1 to 5. cleanliness is NULL when the guest did not answer that question.
Examples
Example 1
Review
| review_id | listing_id | overall | cleanliness | submitted_on |
|---|---|---|---|---|
| 1 | 10 | 5 | 5 | 2025-03-02 |
| 2 | 10 | 5 | 3 | 2025-03-04 |
| 3 | 11 | 4 | 3 | 2025-03-05 |
| 4 | 12 | 5 | NULL | 2025-03-05 |
| 5 | 12 | 5 | 1 | 2025-03-09 |
| 6 | 13 | 4 | 2 | 2025-03-11 |
| 7 | 13 | 2 | 4 | 2025-03-12 |
Output
| review_id | listing_id | gap |
|---|---|---|
| 5 | 12 | 4 |
| 2 | 10 | 2 |
| 6 | 13 | 2 |
How to solve Airbnb: Reviews Where Cleanliness Trails the Overall Rating
Each review is judged on its own row, so no grouping or join is needed: compute overall - cleanliness, keep the rows where it is at least 2, and select it under the name gap.
The skipped question takes care of itself. Arithmetic with NULL yields NULL, and NULL >= 2 is unknown, which WHERE treats as false — so a review with no cleanliness rating drops out without an explicit IS NOT NULL (the alternative writes it anyway, which does no harm and documents the intent). A review where cleanliness is higher than overall gives a negative gap and is excluded, as it should be.
The order is fixed by the statement: biggest gap first, then review_id to break ties between equal gaps. MySQL lets ORDER BY use the select alias gap; the alternative repeats the expression instead. The query is a single scan.
Reference solution (MySQL)
SELECT review_id, listing_id, overall - cleanliness AS gap
FROM Review
WHERE overall - cleanliness >= 2
ORDER BY gap DESC, review_idAnother way
SELECT review_id, listing_id, overall - cleanliness AS gap FROM Review WHERE cleanliness IS NOT NULL AND cleanliness <= overall - 2 ORDER BY overall - cleanliness DESC, review_id ASC← Airbnb: Average Cleaning Fee by Property Type · Airbnb: Experience Bookings and Guests per Category →