Proposed greatest hits

1

Please login or register to vote for this query.

(click on this box to dismiss)

History

Q&A for historians and history buffs

with anon_votes as (
  select count(case VoteTypeId when 2 then 1 end) upvotes,
         count(case VoteTypeId when 3 then 1 end) downvotes,
         PostId
  from PostFeedback
  where (##days?0## = 0 or CreationDate > getdate() - ##days##)
  group by PostId
),

anon_Wilson_rank as (
  select upvotes, PostId, downvotes,
         -- http://www.evanmiller.org/how-not-to-sort-by-average-rating.html
         ((upvotes + 1.9208) / (upvotes + downvotes) - 
                   1.96 * SQRT((upvotes * downvotes) / (upvotes + downvotes) + 0.9604) / 
                   (upvotes + downvotes)) / (1 + 3.8416 / (upvotes + downvotes)) Wilson_rank
  from anon_votes
  where upvotes + downvotes > 0
)
  
select top ##topX?100## rank() over (order by  Wilson_rank desc) rank,
       PostId as [Post Link], pt.Name, Score, 
       upvotes, downvotes, Wilson_rank
from anon_Wilson_rank
     join Posts p on PostId = p.Id
     join PostTypes pt on pt.Id = PostTypeId
--where PostTypeId = 2
order by  Wilson_rank desc

Enter Parameters

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