Q&A about the site for serious players and enthusiasts of poker
with p as ( select id from PostsWithDeleted where PostTypeId = 2 ) ,a as ( select p.id, sum(case when v.votetypeid = 2 then 1 else 0 end) as upvotes, sum(case when v.votetypeid = 3 then 1 else 0 end) as downvotes from p join votes v on p.id = v.PostId group by p.id ), m as ( select upvotes, downvotes, count(id) as cnt, case when upvotes = 0 then 'up = 0' when upvotes = 1 then 'up = 1' when upvotes <= 5 then '2 <= up <= 5' when upvotes <= 10 then '6 <= up <= 10' else '11 <= up' end as part from a group by upvotes, downvotes ), sm as ( select upvotes, sum(cnt) vsum from m group by upvotes ) select m.downvotes, m.part, sum(cnt),sum(vsum), (1.00*sum(cnt)/sum(vsum)) from m join sm on m.upvotes = sm.upvotes group by m.downvotes, m.part order by m.part, m.downvotes