2

​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...)

2
  • I also get 44, which point to the userid not being read correctly.
    – ChrisF Mod
    Commented Dec 1, 2010 at 11:27
  • I don't understand your bonus question.
    – Jon Seigel
    Commented Dec 1, 2010 at 19:07

3 Answers 3

5

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...

2
  • 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. Commented Dec 1, 2010 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
    Commented Dec 1, 2010 at 22:46
1

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..

4
0

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
2
  • 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
    Commented Dec 1, 2010 at 20:13
  • @Michael, correct. Commented Dec 2, 2010 at 9:30

You must log in to answer this question.

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