Q&A for active researchers, academics and students of physics
with p as (
where PostTypeId = 2
) ,a as (
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 (
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
group by upvotes, downvotes
), sm as (
select part, sum(cnt) as vsum
group by part
(1.00*sum(cnt) over(partition by m.downvotes,m.part))/sm.vsum
from m join sm on m.part = sm.part
order by m.part, m.downvotes