DECLARE @Question tinyint = 1 DECLARE @Answer tinyint = 2...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Chemistry Meta

Q&A about the site for scientists, academics, teachers, and students in the field of chemistry

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
        , PostTypes.Name AS PostType
        , COUNT(DISTINCT Posts.Id) AS PostCount
        , CAST(SUM(CASE
                       WHEN Votes.VoteTypeId = @UpMod THEN 1
                       ELSE 0
                   END)
               AS numeric(19, 9))
          AS PostUpvotes
        , CAST(SUM(CASE
                       WHEN Votes.VoteTypeId = @DownMod THEN 1
                       ELSE 0
                   END)
               AS numeric(19, 9))
          AS PostDownvotes
    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 Tags.Id <= 160000 AND Tags.Id > 128000 AND PostTags.TagId = Tags.Id
        LEFT JOIN Votes
            ON Posts.Id = Votes.PostId
    GROUP BY Tags.TagName, PostTypes.Name
)

SELECT
      TagName
    , PostType
    , PostCount
    , PostUpvotes
    , PostDownvotes
    , COALESCE((PostUpvotes + @zz / 2 - @z * SQRT(PostUpvotes * PostDownvotes
                  / NULLIF(PostUpvotes + PostDownvotes, 0) + @zz / 4))
                  / (PostUpvotes + PostDownvotes + @zz), 0)
      AS PostRatings
FROM TotalUpDownVotesByTags_VIEW
ORDER BY PostRatings DESC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results