Ratio of Upvotes to Downvotes Received.

0

Please login or register to vote for this query.

(click on this box to dismiss)

Displays the number of upvotes and downvotes received on questions and answers and their ratio for the top 1000 users by reputation. Note that 999999999 represents a user with no downvotes.

Materials Modeling Meta

Q&A for materials modelers and data scientists

-- Ratio of Upvotes to Downvotes Received.
-- Displays the number of upvotes and downvotes received on questions and answers and their ratio for the top 1000 users by reputation. Note that 999999999 represents a user with no downvotes. 
select
    SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) AS [Up Votes],
    SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) AS [Down Votes],
    case when SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) = 0 then 999999999 else
    (CAST(SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) AS float) / 
     CAST(SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) AS float)) end 
       AS [Up Votes to Down Votes],
    u.DisplayName
from
    Votes v 
    inner join Posts p on v.PostId=p.Id
    inner join Users u on u.Id=p.OwnerUserId
group by u.DisplayName, u.Reputation
Having SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) = 0
order by [Down Votes] desc

Enter Parameters

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