Airbnb: Hosts Meeting a Simplified Superhost Bar
Airbnb reviews hosts every quarter for Superhost status. A host-quality analyst wants a first cut using a simplified bar (the thresholds here are made up…
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic, Joins
- Dialect: MySQL
- Problem: #556
Problem statement
Airbnb reviews hosts every quarter for Superhost status. A host-quality analyst wants a first cut using a simplified bar (the thresholds here are made up for practice) over the quarter's reservations:
- at least 3 completed stays;
- no reservation cancelled by the host;
- at least one review, and an average review rating of at least 4.8 across the host's reviews.
Return host_id, completed_stays and avg_rating (rounded to 2 decimal places) for every host who meets all three, in any order. Guest cancellations do not count against a host.
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: Reservation
| Column | Type |
|---|---|
| reservation_id | int |
| host_id | int |
| status | enum(completed, cancelled_by_guest, cancelled_by_host) |
Primary key: reservation_id.
Every reservation that ended in the quarter.
Table: Review
| Column | Type |
|---|---|
| review_id | int |
| reservation_id | int |
| rating | int |
Primary key: review_id.
A guest's review of a completed stay, 1 to 5 stars. A reservation has at most one review.
Examples
Example 1
Reservation
| reservation_id | host_id | status |
|---|---|---|
| 1 | 1 | completed |
| 2 | 1 | completed |
| 3 | 1 | completed |
| 4 | 1 | cancelled_by_guest |
| 14 | 5 | completed |
| 15 | 5 | completed |
| 16 | 5 | completed |
| 17 | 5 | completed |
| 18 | 5 | completed |
| 5 | 2 | completed |
| 6 | 2 | completed |
| 7 | 2 | completed |
| 8 | 2 | cancelled_by_host |
| 9 | 3 | completed |
| 10 | 3 | completed |
| 11 | 3 | completed |
| 12 | 4 | completed |
| 13 | 4 | completed |
Review
| review_id | reservation_id | rating |
|---|---|---|
| 101 | 1 | 5 |
| 102 | 2 | 5 |
| 103 | 3 | 5 |
| 111 | 14 | 5 |
| 112 | 15 | 5 |
| 113 | 16 | 4 |
| 114 | 17 | 5 |
| 115 | 18 | 5 |
| 104 | 5 | 5 |
| 105 | 6 | 5 |
| 106 | 9 | 5 |
| 107 | 10 | 4 |
| 108 | 11 | 4 |
| 109 | 12 | 5 |
| 110 | 13 | 5 |
Output
| host_id | completed_stays | avg_rating |
|---|---|---|
| 1 | 3 | 5 |
| 5 | 5 | 4.8 |
How to solve Airbnb: Hosts Meeting a Simplified Superhost Bar
The bar mixes two grains. Completed stays and host cancellations are counts over a host's reservations; the rating is an average over the host's reviews, which reach the host only through their reservation. The solution computes each in its own CTE, one row per host, and joins them.
In stays, conditional sums count each status: completed_stays and host_cancels. In ratings, each review is joined to its reservation and averaged per host. The final WHERE applies the three rules. The inner join to ratings already drops hosts with no reviews; even with a left join, NULL >= 4.8 would drop them. Guest cancellations appear in neither count, so they cost the host nothing.
The alternative does it in one pass: reservations LEFT JOIN reviews, then HAVING. That works because a reservation has at most one review, so the join cannot duplicate reservations — and COUNT(DISTINCT …) guards the stay count anyway. With several reviews per reservation the one-pass form would inflate the cancellation sum, which is why separate aggregates are the safer default. Each table is read once.
Reference solution (MySQL)
WITH stays AS (
SELECT host_id,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_stays,
SUM(CASE WHEN status = 'cancelled_by_host' THEN 1 ELSE 0 END) AS host_cancels
FROM Reservation
GROUP BY host_id
), ratings AS (
SELECT r.host_id, AVG(v.rating) AS avg_rating
FROM Review v
JOIN Reservation r ON r.reservation_id = v.reservation_id
GROUP BY r.host_id
)
SELECT s.host_id, s.completed_stays, ROUND(g.avg_rating, 2) AS avg_rating
FROM stays s
JOIN ratings g ON g.host_id = s.host_id
WHERE s.completed_stays >= 3 AND s.host_cancels = 0 AND g.avg_rating >= 4.8Another way
SELECT r.host_id,
COUNT(DISTINCT CASE WHEN r.status = 'completed' THEN r.reservation_id END) AS completed_stays,
ROUND(AVG(v.rating), 2) AS avg_rating
FROM Reservation r
LEFT JOIN Review v ON v.reservation_id = r.reservation_id
GROUP BY r.host_id
HAVING COUNT(DISTINCT CASE WHEN r.status = 'completed' THEN r.reservation_id END) >= 3
AND SUM(r.status = 'cancelled_by_host') = 0
AND AVG(v.rating) >= 4.8← Airbnb: Host Display Names With a Last Initial · Airbnb: Host Response Rate Within 24 Hours →