Airbnb: Rank Experiences by a Bayesian Average Rating
The Experiences team sorts a city's page by rating, but a cooking class with one five-star review should not outrank a walking tour with forty glowing ones.
- Difficulty: Medium
- Topics: Aggregation, Subqueries
- Dialect: MySQL
- Problem: #561
Problem statement
The Experiences team sorts a city's page by rating, but a cooking class with one five-star review should not outrank a walking tour with forty glowing ones. So ratings are shrunk towards the platform average: each Experience is treated as if it also had m = 5 extra reviews at the overall mean.
Let v be an Experience's number of reviews, R its average rating, and C the average of every review in the table (all Experiences together). Its Bayesian rating is (v × R + 5 × C) / (v + 5). For every Experience with at least one review, return experience_id, title, reviews (= v), avg_rating (R) and adjusted_rating, both rounded to 2 decimal places. Order by adjusted_rating descending (the rounded value), then reviews descending, then experience_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: Experience
| Column | Type |
|---|---|
| experience_id | int |
| title | varchar |
| city | varchar |
Primary key: experience_id.
One row per Experience a host offers.
Table: ExperienceReview
| Column | Type |
|---|---|
| review_id | int |
| experience_id | int |
| rating | int |
Primary key: review_id.
One guest review, 1 to 5 stars. experience_id always names an Experience.
Examples
Example 1
Experience
| experience_id | title | city |
|---|---|---|
| 1 | Pastel de nata workshop | Lisbon |
| 2 | Sunrise volcano hike | Bali |
| 3 | Street-art walk | Lisbon |
| 4 | Tea ceremony | Kyoto |
| 5 | Spice farm lunch | Goa |
| 6 | Night kayak tour | Goa |
ExperienceReview
| review_id | experience_id | rating |
|---|---|---|
| 1 | 1 | 5 |
| 2 | 2 | 5 |
| 3 | 2 | 5 |
| 4 | 2 | 5 |
| 5 | 2 | 5 |
| 6 | 2 | 5 |
| 7 | 2 | 5 |
| 8 | 2 | 4 |
| 9 | 2 | 5 |
| 10 | 3 | 3 |
| 11 | 3 | 4 |
| 12 | 3 | 3 |
| 13 | 4 | 4 |
| 14 | 4 | 4 |
| 15 | 4 | 5 |
| 16 | 4 | 4 |
| 17 | 6 | 2 |
| 18 | 6 | 3 |
Output
| experience_id | title | reviews | avg_rating | adjusted_rating |
|---|---|---|---|---|
| 2 | Sunrise volcano hike | 8 | 4.88 | 4.62 |
| 1 | Pastel de nata workshop | 1 | 5 | 4.35 |
| 4 | Tea ceremony | 4 | 4.25 | 4.23 |
| 3 | Street-art walk | 3 | 3.33 | 3.89 |
| 6 | Night kayak tour | 2 | 2.5 | 3.73 |
How to solve Airbnb: Rank Experiences by a Bayesian Average Rating
A raw average rewards luck: one five-star review gives 5.00, while an Experience with eight reviews and a single 4 sits at 4.88. The Bayesian average fixes that by mixing each Experience's own average R with the platform mean C, weighted by how much evidence each has: (v·R + m·C) / (v + m). With few reviews the result stays near C; with many it approaches R. In the example the single-review workshop drops below the eight-review hike.
The query needs aggregates at two levels. per_exp groups the reviews by Experience for v and the rating sum s (note v·R = s). overall is one row with the count and sum of every review, so C = s_all / n_all — the mean over reviews, which weighs busy Experiences more, not the mean of the averages. A CROSS JOIN attaches that row to every Experience. Experiences without reviews never form a group and are left out.
The solution clears the fractions: (s + 5·s_all/n_all) / (v + 5) equals (s·n_all + 5·s_all) / (n_all·(v + 5)), one division of integers. The * 1.0000 matters on MySQL, whose division keeps only four extra decimals: without it, a value just below a rounding boundary can round the wrong way. The alternative computes C with window functions over the grouped rows — SUM(SUM(rating)) OVER () — and applies the formula as written. The order uses the rounded score, then the review count, then the id, so it is deterministic.
Reference solution (MySQL)
WITH overall AS (
SELECT COUNT(*) AS n_all, SUM(rating) AS s_all FROM ExperienceReview
), per_exp AS (
SELECT experience_id, COUNT(*) AS v, SUM(rating) AS s
FROM ExperienceReview
GROUP BY experience_id
)
SELECT x.experience_id, x.title, p.v AS reviews,
ROUND(p.s / p.v, 2) AS avg_rating,
ROUND((p.s * o.n_all + 5 * o.s_all) * 1.0000 / (o.n_all * (p.v + 5)), 2) AS adjusted_rating
FROM per_exp p
JOIN Experience x ON x.experience_id = p.experience_id
CROSS JOIN overall o
ORDER BY adjusted_rating DESC, reviews DESC, x.experience_idAnother way
SELECT experience_id, title, reviews, ROUND(r_avg, 2) AS avg_rating,
ROUND((reviews * r_avg + 5 * c_avg) / (reviews + 5), 2) AS adjusted_rating
FROM (
SELECT x.experience_id, x.title, COUNT(*) AS reviews,
SUM(r.rating) * 1.00000000 / COUNT(*) AS r_avg,
SUM(SUM(r.rating)) OVER () * 1.00000000 / SUM(COUNT(*)) OVER () AS c_avg
FROM Experience x
JOIN ExperienceReview r ON r.experience_id = x.experience_id
GROUP BY x.experience_id, x.title
) t
ORDER BY adjusted_rating DESC, reviews DESC, experience_id← Airbnb: Guests Who Came Back to the Same Host Within a Year · Airbnb: Guest Cancellation Rate by Cancellation Policy →