https://www.evanmiller.org/how-not-to-sort-by-average-rating.html
Q&A for speakers of other languages learning English
DECLARE @Question tinyint = 1; DECLARE @Answer tinyint = 2; DECLARE @UpMod tinyint = 2; DECLARE @DownMod tinyint = 3; DECLARE @DownWeight int = 5; DECLARE @z numeric(19, 9) = 1.96; DECLARE @zz numeric(19, 9) = SQUARE(@z); WITH TotalUpDownVotesByPosts AS ( SELECT Posts.Id , PostTypes.Name AS PostType , SUM(IIF(Votes.VoteTypeId = @UpMod, 1, 0)) AS Upvotes , SUM(IIF(Votes.VoteTypeId = @DownMod, 1, 0)) AS Downvotes FROM Posts INNER JOIN PostTypes ON Posts.PostTypeId = PostTypes.Id LEFT JOIN Votes ON Posts.Id = Votes.PostId GROUP BY Posts.Id , PostTypes.Name ), TotalRatingsByPosts AS ( SELECT Id AS [Post Link] , PostType , Upvotes , Downvotes , (CAST(Upvotes AS numeric(19, 9)) + @zz / 2) / (CAST(Upvotes + Downvotes * IIF(PostType = 'Answer', @DownWeight, 1) AS numeric(19, 9)) + @zz) AS Ratings FROM TotalUpDownVotesByPosts ) SELECT RANK() OVER (ORDER BY Ratings DESC) AS [Rank] , * FROM TotalRatingsByPosts ORDER BY Ratings DESC