SE tag ratings

1

Please login or register to vote for this query.

(click on this box to dismiss)

http://www.evanmiller.org/how-not-to-sort-by-average-rating.html

Anime and Manga

Q&A for anime and manga fans

DECLARE @Question tinyint = 1;
DECLARE @Answer tinyint = 2;
DECLARE @UpMod tinyint = 2;
DECLARE @DownMod tinyint = 3;
DECLARE @z numeric(19, 9) = 1.96;
DECLARE @zz numeric(19, 9) = SQUARE(@z);

WITH TotalUpDownVotesByTags_VIEW AS (
    SELECT
          Tags.TagName
        , COUNT(DISTINCT Posts.Id) AS Posts
        , SUM(CASE
                  WHEN Votes.VoteTypeId = @UpMod THEN 1
                  ELSE 0
              END)
          AS Upvotes
        , SUM(CASE
                  WHEN Votes.VoteTypeId = @DownMod THEN 1
                  ELSE 0
              END)
          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_VIEW AS (
    SELECT
          *
        , (CAST(Upvotes AS numeric(19, 9)) + @zz / 2)
          / (CAST(Upvotes + Downvotes AS numeric(19, 9)) + @zz)
          AS TagRatings
    FROM TotalUpDownVotesByTags_VIEW
)

SELECT
      RANK() OVER (ORDER BY TagRatings DESC) AS TagRank
    , *
FROM TotalRatingsByTags_VIEW
ORDER BY TagRatings DESC;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results