7

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?

Improve this question

1 Answer 1

Reset to default
10

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.

A little peek inside the sausage factory

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

Improve this answer
3
  • 4
    This was a really heckin cool explanation, thanks for putting this together.
    – Thomas Markov
    Commented Jan 5, 2023 at 21:08
  • Do I understand correctly that Posts2Votes and RepHistory are tables, which you use internally at StackExchange but do not release to SEDE?
    – paperskilltrees
    Commented Dec 18, 2023 at 18:44
  • 1
    @paperskilltrees Yes, there are many internal tables that are either transformed for or suppressed from SEDE, typically for PII reasons.
    – Aaron Bertrand Staff
    Commented Dec 19, 2023 at 20:13

You must log in to answer this question.

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