Ranking Items by Rating Fairly With a Bayesian Average

One glowing review pushes a little-known item above a consistently strong favorite. A Bayesian average tempers that jump. The weight you choose expresses how much evidence the ranking requires.

Define the Rating Population

Use valid ratings from the intended population. Decide how duplicates, removed reviews, and unrated items are handled. I confirm those rules before calculating a global mean. A clever formula cannot rescue the wrong set of votes.

Write down the question before changing the query. For this example, the useful distinction is between the raw item average and the prior-adjusted score. Those are different pieces of evidence. A result without that distinction leaves you guessing about the next step. Keep the names and units beside the output. You want the next reader to understand the same thing you understood, without needing your memory of the query window.

In the sample, the five ratings sum to 23.4. Divided by five votes, the global mean is 4.68.

CREATE TABLE #Ratings(ItemId int,rating decimal(4,2) CHECK(rating BETWEEN 1 AND 5));
INSERT #Ratings VALUES(1,5),(2,4.8),(2,4.8),(2,4.8),(3,4);

Aggregate Votes per Item

COUNT supplies the evidence weight and AVG supplies the item mean. Cast integer ratings to a decimal before averaging. Keep counts beside scores. A display that hides vote volume also hides why the adjustment exists.

Use a separate test database for the examples that create objects. Read each statement before running the next one. The sample values are deliberately small and illustrative. They explain the rule without claiming a production result. Replace them with a copy of your own data only after the basic behavior is clear. Keep the chosen evidence weight in view when you make that change. A larger input does not change the meaning of the rule.

GROUP BY ItemId sets the grain: one row per item. In my test, the sample produced three rows with vote counts of 1, 3, and 1, and item 2 averaged 4.8. If an item shows more rows than expected, a second column slipped into the grouping. An item with no ratings produces no row at all, so a catalog list needs a LEFT JOIN from the item table.

Use a Vote-Weighted Global Mean

Compute the global mean from total rating sum divided by total vote count. Do not average the item averages without weights. The latter gives one-review items the same influence as heavily reviewed items.

Try one perfect rating before accepting the first result. That case tells you whether the example handles the boundary you actually care about. Look at the returned values, not just the message saying the statement completed. Successful execution and a correct answer are separate checks. Save the exact input that exposed a difference. It gives you a repeatable test for the next change and keeps the discussion tied to evidence.

DECLARE @m decimal(18,4)=10;
WITH Item AS(SELECT ItemId,COUNT(*) AS n,AVG(rating) AS item_mean,SUM(rating) AS rating_sum FROM #Ratings GROUP BY ItemId),
 Global AS(SELECT *,SUM(rating_sum) OVER()/NULLIF(SUM(CAST(n AS decimal(18,4))) OVER(),0) AS global_mean FROM Item),
 Scored AS(SELECT *,(n*item_mean+@m*global_mean)/(n+@m) AS adjusted_score FROM Global)
SELECT ItemId,n,item_mean,global_mean,adjusted_score,
 DENSE_RANK() OVER(ORDER BY adjusted_score DESC) AS rating_rank FROM Scored ORDER BY rating_rank,ItemId;

Apply the Prior Weight in the Bayesian Average

The score is (n times item mean plus m times global mean) divided by n plus m. The minimum-votes constant m is a prior strength. It is not a magic threshold at which an item suddenly becomes trustworthy.

Now compare different prior weights on the same votes with the original input. Change one part at a time. If you change the data, the query, and the session settings together, the comparison loses its meaning. Keep the result columns visible while you work. A difference is useful only when you can explain which rule produced it. When the output surprises you, reduce the example until the reason becomes clear rather than adding another layer of SQL.

Rank the Bayesian Average With a Clear Tie Rule

Use DENSE_RANK over descending adjusted score. Show the raw average and vote count too. I keep the prior visible in the report. A star rating should not arrive wearing an unexplained disguise.

I check the raw item average before I trust the final answer. It is easy to focus on the visible symptom and overlook the input that created it. Ask yourself: does the prior-adjusted score support the decision you are about to make? Keep a second example that disagrees with your first assumption. A check that only confirms the easy case is comforting, but it does not protect the next person who uses the script.

Compare Several Chosen Weights

Evaluate plausible m values and inspect changed ordering. In the sample, m of 10 still leaves the single five-star item barely ahead, 4.709 to 4.708. At m of 20, the item with three votes moves to first. Choose the weight from the site’s evidence policy and validation, not from the ranking you prefer. Keep the policy stable across reporting periods unless the business explicitly changes it.

Include two items with tied adjusted scores in your review. The quiet case matters as much as the busy one. Define what an empty result means and what an error means. Do not treat the two as interchangeable. If another process changes the same data, decide who owns the comparison and when it is valid. Record that boundary beside the script. The next run should not depend on someone remembering an unwritten rule.

WITH Item AS(SELECT ItemId,COUNT(*) AS n,AVG(rating) AS item_mean,SUM(rating) AS total_rating FROM #Ratings GROUP BY ItemId),
GlobalMean AS(SELECT *,SUM(total_rating) OVER()/SUM(CAST(n AS decimal(18,4))) OVER() AS global_mean FROM Item)
SELECT w.prior_weight,i.ItemId,i.n,i.item_mean,
 (i.n*i.item_mean+w.prior_weight*i.global_mean)/(i.n+w.prior_weight) AS adjusted_score
FROM GlobalMean AS i CROSS JOIN(VALUES(5),(10),(20)) AS w(prior_weight)
ORDER BY w.prior_weight,adjusted_score DESC,i.ItemId;

Remember the Limits of a Bayesian Average

A Bayesian average reduces volatility from small counts. It does not detect fraudulent reviews or account for every difference in reviewer behavior. Validate the source and explain the ranking policy before presenting the score as objective truth.

I keep the chosen evidence weight in the handoff notes because that is where shortcuts return. Give the next DBA the query, the interpretation, and the condition that makes the result unreliable. Keep the original evidence when you change the implementation. Repeat the same boundary checks afterward. You then have a practical way to judge the change, instead of a general feeling that the new version looks better.

Related reading on this blog: How to Find Stored Procedure Execution Count and Average Elapsed Time? and Need Your Feedback: Next Action Items in SQL in Sixty Seconds.

A fair ranking is not just an average, it is an average with an evidence policy.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.