# Upvote distribution SEDE query

There is a 1-to-many relationship between the posts table and the votes table, and they join on posts.id = votes.postid. I would like to know how many posts have 1, 2, 3, 5, etc. votes.

Thanks to Waffles' most controversial posts query, it was trivial to write this:

declare @VoteStats table (PostId int, up int)

set nocount on

insert @VoteStats
select
PostId,
up = sum(case when VoteTypeId = 2 then 1 else 0 end)
from Votes
where VoteTypeId in (2,3)
group by PostId

set nocount off

select up, count(*)
from @VoteStats
group by up;


However, it runs in a full 22 seconds on the Stack Overflow data dump.

Am I overlooking an obvious way to speed this query up, perhaps avoiding the creation of the VoteStats table?

• Is there some reason you can't restrict your where clause to where VoteTypeId = 2 and then just count(*) the rows? That's essentially what you're currently asking. Or do you also have a down column that goes the other way? – Clockwork-Muse Sep 7 '11 at 17:47
• @X-Zero That only shoved off one second, but makes a lot of sense. Plus, it removed a subtle problem with showing a count of posts with "0 votes" (they actually aren't all posts with no votes). I should've totally seen that – badp Sep 7 '11 at 17:51
• Then that's probably as fast as it's going to get... – Clockwork-Muse Sep 7 '11 at 17:57

## 2 Answers

Not creating the VoteStats table makes a big difference. Gets it down to ~5.5 seconds.

set nocount on

select up, count(*)
from (
select
PostId,
up = sum(case when VoteTypeId = 2 then 1 else 0 end)
from Votes
where VoteTypeId in (2,3)
group by PostId
) a
group by up
order by up


http://data.stackexchange.com/stackoverflow/q/112108/

From the execution plan of the table variable version, it looks like the table scan and the sort on it is pretty expensive.

• ...and combining this with the other answer cuts the time down to 2 seconds. Pesky syntax! :D – badp Sep 8 '11 at 22:45

I think this should be faster, because instead of summing up 1 for every vote that has a VoteTypeId=2, I counted votes with VoteTypeId=2:

with VoteCountsByPost as
(
select
p.Id as PostId,
count(v.Id) as up
from Posts p
left join Votes v on v.PostId = p.Id and v.VoteTypeId = 2
group by p.Id
)
select up, count(*)
from VoteCountsByPost
group by up
order by up


Also, please note that the results are a little different: you query does not take into account posts that have no votes.

For instance, if a post has a vote with VoteTypeId != 2, then that post will appear with 0 ups. If it has no votes whatsoever, then that post will be completely excluded from the result set.

I don't know if this is the intended behavior.

• Yeah, this is what X-Zero suggested. See the comments on the question for my follow up. :) – badp Sep 8 '11 at 22:41