Posts más controversiales

1

Please login or register to vote for this query.

(click on this box to dismiss)

Looks for posts with more than half the amount of downvotes as they have upvotes Ordered by upvotes

Amateur Radio

Q&A for amateur radio enthusiasts

set nocount on 

declare @VoteStats table (PostId int, up int, down int) 

insert @VoteStats
select
    PostId, 
    up = sum(case when VoteTypeId = 2 then 1 else 0 end),
    down = sum(case when VoteTypeId = 3 then 1 else 0 end)
from Votes
where VoteTypeId in (2,3)
group by PostId

set nocount off

select top 2000
p.id as [Post Link] ,
up,
down,
ABS( (1+ABS(2*down/up-1))*ABS(up-down)-1) as [controversia]

from @VoteStats 
join Posts p on PostId = p.Id
where
p.CommunityOwnedDate is null
and p.ClosedDate is null
and (up-down != up
and -up+down != down)

order
by ABS( (1+ABS(2*down/up-1))*ABS(up-down)-1) desc,
up desc,
down desc,
title asc

Enter Parameters

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