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