Q&A for professional and independent game developers
with --question ids 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 part, sum(cnt) as vsum from m group by part ), pm as ( select m.downvotes, m.part, sum(cnt) as psum from m group by m.downvotes, m.part ) select log(pm.downvotes+1,10), pm.part, log(1.00*psum/vsum,10) from pm join sm on pm.part = sm.part order by pm.part, pm.downvotes