Ratio of downvotes per total votes (of tags with 1000+ total votes)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Divides the downvotes by total votes. Does so only for tags with more than 1000 total votes. Orders by ratio, descending ------------ Shows the tags with highest downvote ratio at the top.

Stack Overflow

Q&A for professional and enthusiast programmers

WITH TotalsByTag (tagName, totalUpVotes, totalDownVotes) AS 
    (SELECT Tags.tagName,
        totalUpVotes = SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END),
        totalDownVotes = SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END)
     FROM Tags
     INNER JOIN PostTags ON PostTags.TagId = Tags.Id
     INNER JOIN Votes ON Votes.PostId = PostTags.PostId
     WHERE Votes.VoteTypeId IN (2,3)
     GROUP BY Tags.tagName)
     
SELECT tagName, 
       CASE
           WHEN totalUpVotes = 0 AND totalUpVotes = 0 THEN 0
           ELSE ROUND(CAST(totalUpVotes AS FLOAT) / (totalDownVotes), 3)
       END ratioOfDownVotes
FROM TotalsByTag
WHERE (totalUpVotes + totalDownVotes) > 1000
ORDER BY 2 DESC

Enter Parameters

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