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 = 3; DECLARE @z numeric(19, 9) = 1.96; DECLARE @zz numeric(19, 9) = SQUARE(@z); WITH TotalUpDownVotesByTags AS ( SELECT Tags.TagName , COUNT(DISTINCT Posts.Id) AS Posts , 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 INNER JOIN PostTags ON (CASE WHEN Posts.PostTypeId = @Question THEN Posts.Id WHEN Posts.PostTypeId = @Answer THEN Posts.ParentId END) = PostTags.PostId INNER JOIN Tags ON PostTags.TagId = Tags.Id LEFT JOIN Votes ON Posts.Id = Votes.PostId GROUP BY Tags.TagName ), TotalRatingsByTags AS ( SELECT * , (CAST(Upvotes AS numeric(19, 9)) + @zz / 2) / (CAST(Upvotes + Downvotes * @DownWeight AS numeric(19, 9)) + @zz) AS Ratings FROM TotalUpDownVotesByTags ) SELECT RANK() OVER (ORDER BY Ratings DESC) AS [Rank] , * FROM TotalRatingsByTags ORDER BY Ratings DESC