8

In the Stack Exchange Data Explorer (SEDE), votes are not recorded with the user that cast them, for privacy reasons, except in cases of bounties and favorite votes.

However, for answer accept votes (VoteTypeId = 1), you can still find out who accepted an answer, by doing a double join on posts, like so:

SELECT 
  p2.OwnerUserID AS [Voter_ID]
, v.Id AS [Vote_ID]
FROM Votes AS v
INNER JOIN Posts AS p1 ON v.PostId = p1.Id
INNER JOIN Posts AS p2 ON p1.ParentId = p2.Id

this works, but drastically complicates and slows down queries analyzing stats about accepted answer votes and similar.

It's obvious and desirable that Voter information is scrubbed for normal and secret votes in SEDE, but since an intended workaround exists, and since answer accepts are public anyways, I'd like to request a modification to the script that scrubs the UserId field from Votes to allow Accepted Answer Voters UserId to persist.

2

While I'm not opposing to having publicly available data also in SEDE let me offer in the mean time (for 6 to 8 weeks) a slightly better (based in the Execution plan) query:

SELECT p2.OwnerUserID AS [Voter_ID]
     , v.Id AS [Vote_ID]
FROM Votes AS v
INNER JOIN Posts AS p2 ON p2.AcceptedAnswerId = v.PostId
where v.votetypeid = 1 

which removes an extra join on the Post table (view really)

That has the following plan:

enter image description here

which seems a lot less involved then your query:

enter image description here

I did try to create a temptable for Votes and an index on it but the spooling of data and the index insert take considerable longer.

One thing that might have less impact and might help more queries is to ask for an extra index on the Votes table. Specially on Postid would help in joins and slicing that huge table.

You must log in to answer this question.

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