Most viewed questions lately

0

Please login or register to vote for this query.

(click on this box to dismiss)

Arqade

Q&A for passionate videogamers on all platforms

with anon_votes as (
  select count(case VoteTypeId when 2 then 1 end) upvotes,
         count(case VoteTypeId when 3 then 1 end) downvotes,
         isnull(ParentId, PostId) PostId
  from PostFeedback pf
       join Posts p on PostId = p.Id
  where pf.CreationDate > getdate() - ##days?7##
  group by isnull(ParentId, PostId)
),

anon_Wilson_score 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_score
  from anon_votes
  where upvotes + downvotes > 0
)
  
select top ##topX?100## PostId as [Post Link], Score, 
       upvotes, downvotes, round(Wilson_score, 2) Wilson_score, ViewCount, Tags
from anon_Wilson_score
     join Posts p on PostId = p.Id
--where PostTypeId = 1
order by upvotes+downvotes desc

Enter Parameters

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