How can I find unaccepted answers using the SEDE?

Question

While researching my question, "Are there any questions for which multiple answers have received enlightened badges?", I've created a query that searches for questions with multiple accepted answers. By the looks of it, all these questions have all but one accepted answer deleted, and sometimes all of them.

From what I gather from answers such as this one, when an answer is unaccepted (either explicitly or by accepting a different answer), its acceptance vote is deleted (and rep recalculated). This does not happen when that accepted answer is already deleted. That explains why my results show only questions where the previously accepted answers have been deleted.

But is there a way to find all answers that were once accepted but were unaccepted later, either explicitly or by accepting another answer?

Answer 1

Unfortunately SEDE does not contain the additional information required to satisfy this request.

When a post is unaccepted by the asker:

1. its row in Posts2Votes is soft deleted (DeletionDate is changed from NULL to {current date/time}, but as far as I can tell the TargetRepChange and VoterRepChange values remain intact).
2. a row is added to RepHistory with a RepHistoryTypeId of 2 ("Asked (sic) Unaccepts Answer"), whether it changes the answer author's rep or not. This is what's used to recalc.
3. (separate, but related) if a different post is accepted, a new row is added to Posts2Votes.

Only votes that have not been deleted (DeletionDate is still NULL) make it to SEDE's Votes table, which is a masked subset, as part of the weekly refresh. The SEDE version of Stack Overflow's Votes table has ~225MM rows, which - other than a few days of drift - matches the number of rows in production with a DeletionDate of NULL. The Posts2Votes table has an additional ~33MM rows where the DeletionDate is present and so these rows are not found in the Votes table in SEDE.

Even if those rows were included in the Votes table, there is additional information required to piece together whether it was a natural unaccept, or the author (or a moderator) deleted the answer, or the question was deleted (or possibly even migrated?). Which may or may not be useful differentiation for what you would have planned to do with this information. Some of those could be answered by extending the query, but some would require (a) RepHistory to be included in SEDE, and/or (b) user information to be unmasked in Votes. Both of which are very much the way they are by design.

The only ones you can find today are the ones where the answer was deleted, not where the accept was removed. The first query in your example (for post ID 90) had an accepted answer of 94, but it was deleted by a moderator, not unaccepted. So the accept vote is still in Votes, which is why you can determine that it was accepted once. If the post were still published, any info that would help identify it as a formerly-accepted answer would simply not be accessible to your query.

As an experiment, I flipped the accept away from (and then back to) my own self-answer on this question, then looked in the database.

The vote in red will never make it to SEDE, and the vote in orange will disappear in the next refresh (and hence won't appear in the results of this query). The one in blue (purple?) was the original accept before I accepted my own answer, and hasn't been in SEDE for years (if ever). Right now that query returns 30114577 but after the next refresh it will only return 262596301.

I just can't prove that to you until next week (and hope a moderator doesn't delete my answer in the meantime). :-)

_{(The votes with RepChange of 0 are the accept/unaccept of my own self-answer.)}