1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

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
  group by PostId
),

reg_votes as (
  select count(case VoteTypeId when 2 then 1 end) upvotes,
         count(case VoteTypeId when 3 then 1 end) downvotes,
         PostId
  from Votes
  group by PostId
),

all_votes as (
  select isnull(r.PostId, a.PostId) PostId,
         isnull(a.upvotes, 0)   + isnull(r.upvotes, 0)   upvotes, 
         isnull(a.downvotes, 0) + isnull(r.downvotes, 0) downvotes
  from anon_votes a
       full outer join reg_votes r on a.PostId = r.PostId
),

Wilson_score as (
  select PostId, upvotes, 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 all_votes
  where upvotes + downvotes > 0
  group by upvotes, PostId, downvotes
)
  
select top ##topN?100## p.Id as [Post Link], Score, 
       a.upvotes anon_up, a.downvotes anon_down, Wilson_score, CreationDate
from Wilson_score w
     join Posts p on w.PostId = p.Id
     left join anon_votes a on a.PostId = p.Id
where PostTypeId = 2
      and datediff(##period?week##, 
                   p.CreationDate, 
                   (select max(CreationDate) from Posts)) <= 1
      and w.upvotes + w.downvotes > 0 -- 'Divide by zero error encountered.' ?
      and Wilson_score > 0.5 
      and Wilson_score = (select max(Wilson_score)
                  from Wilson_score
                       join Posts a on a.Id = PostId
                  where p.ParentId = a.ParentId
                  group by ParentId
                  )
order by  Wilson_score desc

fork query

edited aug 12 14

created aug 12 14 Jon "not doe" Ericson