8

I am creating a query on data explorer in which I want to find out how many accepted answers were re-assigned (a different answer was accepted in the same question before, but a different question was accepted later) and has a score of 15. Is there a way to do that? Here is what I have so far:

-- Print out how many answers have accepted answers that 
-- have been re-assigned and have a score of fifteen.
-- Print them and the link to the answer owner

SELECT
  count(posts.AcceptedAnswerId) as [Accepted Answers]


FROM 
  posts

WHERE
      posts.postTypeId = 1
  and posts.Score >= 15
  -- Peramater for having the second accepted answer (accepted answer re-awarded)
8

You might think that the following query seems to do that but renders a far by complete result.

with m_acc as (
select p.parentid as qid
, count(*) as cnt
from posts p
inner join votes  v on v.postid = p.id
where votetypeid = 1 -- AcceptedByOriginator
group by p.parentid
having count(*) > 1
)

select id as [Post Link]
     , score 
     , m_acc.cnt as [# times accept switch]
from posts p
inner join m_acc on qid = p.id
order by score desc

Remarkable enough only 57 questions seem to match that for Stack Overflow. Uncertain if that was a correct number, I checked one which was correct so I assumed the other results are sound as well. But checking a couple of known positives from the Tavern and SOCVR it turns out that a lot of data is missing. The outcome here is probably due to invalid data.

Further investigation of two posts with this query only showed one accept record while the post timeline of both posts show TWO accept votes.

That leads me to conclude that the votes table is cleaned/sanitized to only contain the current accepted answer vote and not the history of those votes.

With that hope shattered on being able to answer your question I switched to looking at the posthistory table. Unfortunately that table doesn't have a record for accept votes either which leads to this unsatisfying answer:

You can't query for those questions because the data needed isn't available in any table available in SEDE.

5
  • Amazing! How did you get to the post timeline? I don't see a way to do it. – Caleb Kleveter Nov 19 '15 at 17:57
  • 1
    @CalebKleveter see this answer: meta.stackexchange.com/a/76569/158100 – rene Nov 19 '15 at 18:03
  • 1
    @CalebKleveter The outcome is wrong, the data needed to query this probably shouldn't exist in SEDE but it does for 57 posts. – rene Nov 19 '15 at 20:27
  • Oh, so the query you wrote actually doesn't do what we thought it did? – Caleb Kleveter Nov 19 '15 at 20:35
  • 1
    right, I updated my answer to reflect that. You could post a feature request to get that added but you need to have a clear goal how that data helps the community – rene Nov 19 '15 at 20:47

You must log in to answer this question.

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