Airbnb: Rank Experiences by a Bayesian Average Rating

Medium
561.

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

ColumnType
experience_idint
titlevarchar
cityvarchar

Primary key: experience_id.

One row per Experience a host offers.

Table: ExperienceReview

ColumnType
review_idint
experience_idint
ratingint

Primary key: review_id.

One guest review, 1 to 5 stars. experience_id always names an Experience.

Example

The rows must come back in the order shown.

Experience

experience_idtitlecity
1Pastel de nata workshopLisbon
2Sunrise volcano hikeBali
3Street-art walkLisbon
4Tea ceremonyKyoto
5Spice farm lunchGoa
6Night kayak tourGoa

ExperienceReview

review_idexperience_idrating
115
225
325
425
525
625
725
824
925
1033
8 more rows

Output

experience_idtitlereviewsavg_ratingadjusted_rating
2Sunrise volcano hike84.884.62
1Pastel de nata workshop154.35
4Tea ceremony44.254.23
3Street-art walk33.333.89
6Night kayak tour22.53.73
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: 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

ColumnType
experience_idint
titlevarchar
cityvarchar

Primary key: experience_id.

One row per Experience a host offers.

Table: ExperienceReview

ColumnType
review_idint
experience_idint
ratingint

Primary key: review_id.

One guest review, 1 to 5 stars. experience_id always names an Experience.

Examples

Example 1

Experience

experience_idtitlecity
1Pastel de nata workshopLisbon
2Sunrise volcano hikeBali
3Street-art walkLisbon
4Tea ceremonyKyoto
5Spice farm lunchGoa
6Night kayak tourGoa

ExperienceReview

review_idexperience_idrating
115
225
325
425
525
625
725
824
925
1033
1134
1233
1344
1444
1545
1644
1762
1863

Output

experience_idtitlereviewsavg_ratingadjusted_rating
2Sunrise volcano hike84.884.62
1Pastel de nata workshop154.35
4Tea ceremony44.254.23
3Street-art walk33.333.89
6Night kayak tour22.53.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_id

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