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.

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.
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, u.Reputation,
    case when u.DisplayName='Randolpho' then 'THATS ME!!!' else ' ' end
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