1

Please login or register to vote for this query.

(click on this box to dismiss)

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 TotalUpDownVotesByTags AS (
    SELECT
          Tags.TagName
        , PostTypes.Name AS PostType
        , 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
        , PostTypes.Name
)
SELECT
      TagName AS Tags
    , PostType AS [Post Type]
    , Posts
    , 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 TotalUpDownVotesByTags
ORDER BY
    Ratings DESC

view current version

edited sep 10

created jan 30 18 Gao