What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 134 Stack Exchange communities.

​I was trying to use the Data Explorer for Stack Exchange to count how many votes I gave to questions and how many I gave to answers on Stack Overflow.

Looking at the schema, I've assumed you can distinguish between questions and answers basing on the ParentId. Questions have ParentId = null. This is just speculation based on the name of the fields, so if this is a wrong assumption please let me know it.

What I've done:

declare @UserId int = ##UserId##

​select count(*) as VotesOnQuestions
from Votes join Posts
on Votes.PostId = Posts.Id
where ​UserId = @UserId and ParentId is null

The point is that, for my account, this returns a mere 44 for questions and 0 for answers, which can't be correct since I gave more than 500 votes so far. So:

  • The dump for the DB is veeeeeery old
  • My assumption about how to distinguish questions and answers is wrong

Any idea?

Bonus question: I'm completely new to Sql Server. If you need, as in this case, to return the number of elements with a null field and the number of complementary fields (all - null), how to structure the query to avoid running it twice? (bu this is probably more for Stack Overflow...)

share|improve this question
    
I also get 44, which point to the userid not being read correctly. –  ChrisF Dec 1 '10 at 11:27
    
I don't understand your bonus question. –  Jon Seigel Dec 1 '10 at 19:07

3 Answers 3

The data dump doesn't contain the data you want - the data dump, and therefore SEDE only contains data that is available from the website. You can't see who voted for what on the website so it's not in the dump. (You can see that there's over 8 million votes with a null userid in there currently.) The 44 votes you are seeing would be your favourite questions when the last dump was made. You can't favourite an answer, hence the 0...

share|improve this answer
    
To clarify this in the context of the query posted by the op, the UserID in that column is the Id of the user a vote was posted to, rather than the id of the user a vote was posted by. –  Joel Coehoorn Dec 1 '10 at 19:56
    
@Joel: No, the UserId is "posted by." For a vote on a question (for example), the post we already know is owned by OwnerUserId in Posts. Therefore, the UserId in Votes has to be the person who voted. –  Jon Seigel Dec 1 '10 at 22:46

You could also just use PostTypeId which will be 1 for questions and 2 for answers. We now have wiki type posts as well which are 3 and beyond..

share|improve this answer
    
Weird there's no PostTypes table available (as in VoteTypes)... Thanks. –  Roberto Aloi Dec 1 '10 at 11:05
    
This also implies that my assumption was correct, since I get the same numbers. Should I then assume the dump hasn't been updated for quite a while? –  Roberto Aloi Dec 1 '10 at 11:08
    
@rob nope it was updated in November see blog.stackoverflow.com/2010/11/… –  Jeff Atwood Dec 1 '10 at 11:13
    
So how come I get that 44? My query is at odata.stackexchange.com/stackoverflow/s/678/… –  Roberto Aloi Dec 1 '10 at 11:37

As was already mentioned, the information you want is not available in the data dump, and that would break vote anonymity. I'm posting now to answer the bonus question:

If you need... to return the number of elements with a null field ... how to structure the query to avoid running it twice?

You can do that like this:

SELECT Columns 
FROM TableA a 
LEFT JOIN TableB b ON a.Column = b.Column 
WHERE b.Column IS NULL
share|improve this answer
1  
I think he wanted to calculate both the number of elements with a null field and the number of elements without a null field, at the same time. That's why he mentioned running it twice. –  mmyers Dec 1 '10 at 20:13
    
@Michael, correct. –  Roberto Aloi Dec 2 '10 at 9:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .