Airbnb: Reviews Where Cleanliness Trails the Overall Rating

Easy
551.

Airbnb: Reviews Where Cleanliness Trails the Overall Rating

EasyBasics

MySQL

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

ColumnType
review_idint
listing_idint
overallint
cleanlinessint
submitted_ondate

Primary key: review_id.

Ratings are whole stars from 1 to 5. cleanliness is NULL when the guest did not answer that question.

Example

The rows must come back in the order shown.

Review

review_idlisting_idoverallcleanlinesssubmitted_on
110552025-03-02
210532025-03-04
311432025-03-05
4125NULL2025-03-05
512512025-03-09
613422025-03-11
713242025-03-12

Output

review_idlisting_idgap
5124
2102
6132
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: 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

ColumnType
review_idint
listing_idint
overallint
cleanlinessint
submitted_ondate

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_idlisting_idoverallcleanlinesssubmitted_on
110552025-03-02
210532025-03-04
311432025-03-05
4125NULL2025-03-05
512512025-03-09
613422025-03-11
713242025-03-12

Output

review_idlisting_idgap
5124
2102
6132

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_id

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