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 TotalUpDownVotesByPosts_VIEW AS (
    SELECT
          Posts.Id
        , PostTypes.Name AS PostType
        , SUM(CASE
                  WHEN Votes.VoteTypeId = @UpMod THEN 1
                  ELSE 0
              END)
          AS PostUpvotes
        , SUM(CASE
                  WHEN Votes.VoteTypeId = @DownMod THEN 1
                  ELSE 0
              END)
          AS PostDownvotes
    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_VIEW AS (
    SELECT
          Id AS [Post Link]
        , PostType
        , PostUpvotes
        , PostDownvotes
        , (CAST(PostUpvotes AS numeric(19, 9)) + @zz / 2)
              / (CAST(PostUpvotes + PostDownvotes * CASE WHEN PostType = 'Answer' THEN @DownWeight ELSE 1 END AS numeric(19, 9)) + @zz)
          AS PostRatings
    FROM TotalUpDownVotesByPosts_VIEW
)
SELECT
      RANK() OVER (ORDER BY PostRatings DESC) AS PostRank
    , *
FROM TotalRatingsByPosts_VIEW
ORDER BY PostRatings DESC;

view current version

edited sep 10

created aug 29 Gao