# Thinking outside the box

Patron Saint of Lost Yaks

## Bayesian Estimate and Root Mean Square

Sometimes you face the situation where you have some items ranked, and someone always tries to bump the ranking by voting a "perfect 100" to a particular item.

And when you rank the items with average function or root mean square, that single "perfect 100" vote still bumps the item at top of ranking.

Well, have you looked a Bayesian Estimate?

DECLARE       @Sample TABLE
(
userID INT,
vote INT
)
INSERT @Sample
SELECT 3, 40 UNION ALL
SELECT 3, 60 UNION ALL
SELECT 0, 100 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 2, 60 UNION ALL
SELECT 2, 70 UNION ALL
SELECT 2, 100 UNION ALL
SELECT 2, 90 UNION ALL
SELECT 2, 60 UNION ALL
SELECT 2, 20

-- Average
SELECT        userID,
AVG(1.0E * vote) AS Average
FROM          @Sample
GROUP BY      userID
ORDER BY      userID

-- Root Mean Square
SELECT        userID,
SQRT(SUM(Items * thePow) / SUM(Items)) AS [Root Mean Square]
FROM          (
SELECT        userID,
POWER(vote, 2.0E) AS thePow,
1.0E * COUNT(*) AS Items
FROM          @Sample
GROUP BY      userID,
vote
) AS d
GROUP BY      userID
ORDER BY      userID

-- Bayesian estimate
SELECT        s.userID,
1.0E * COUNT(*) /(1.0E * COUNT(*) + @minVotes) * AVG(s.vote) +(@minVotes /(1.0E * COUNT(*) + @minVotes)) * MIN(c.c) AS [Bayesian estimate]
FROM          @Sample AS s
CROSS JOIN    (
SELECT AVG(1.0E * vote) AS c
FROM   @Sample
) AS c
GROUP BY      s.userID