Airbnb: Hosts Meeting a Simplified Superhost Bar

Medium
556.

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

ColumnTypeValues
reservation_idint
host_idint
statusenumcompleted, cancelled_by_guest, cancelled_by_host

Primary key: reservation_id.

Every reservation that ended in the quarter.

Table: Review

ColumnType
review_idint
reservation_idint
ratingint

Primary key: review_id.

A guest's review of a completed stay, 1 to 5 stars. A reservation has at most one review.

Example

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

Reservation

reservation_idhost_idstatus
11completed
21completed
31completed
41cancelled_by_guest
145completed
155completed
165completed
175completed
185completed
52completed
8 more rows

Review

review_idreservation_idrating
10115
10225
10335
111145
112155
113164
114175
115185
10455
10565
5 more rows

Output

host_idcompleted_staysavg_rating
135
554.8
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: 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

ColumnType
reservation_idint
host_idint
statusenum(completed, cancelled_by_guest, cancelled_by_host)

Primary key: reservation_id.

Every reservation that ended in the quarter.

Table: Review

ColumnType
review_idint
reservation_idint
ratingint

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_idhost_idstatus
11completed
21completed
31completed
41cancelled_by_guest
145completed
155completed
165completed
175completed
185completed
52completed
62completed
72completed
82cancelled_by_host
93completed
103completed
113completed
124completed
134completed

Review

review_idreservation_idrating
10115
10225
10335
111145
112155
113164
114175
115185
10455
10565
10695
107104
108114
109125
110135

Output

host_idcompleted_staysavg_rating
135
554.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.8

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