1

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.

Q&A for professional and enthusiast programmers

-- 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
where 
    u.Id in (select top 1000 id from users order by Reputation desc)
group by u.DisplayName, u.Reputation
order by [Up Votes to Down Votes] desc

fork query

created jul 2 10 randolpho