Get all Posts with Self-Accepted Answers in SEDE (Stack Exchange Data Explorer)

Question

As stated in the title, we're looking to get all Posts (including Users, Comments and Suggested Edits) where the original question-asker accepted their own answer as the best answer. In other words, all cases where OwnerUserId for PostTypeId 1 = OwnerUserId for PostTypeId 2.

I'm struggling with this as I don't have a deep understanding of the SEDE DB schema.

This is from SEDE examples that gets Users with high self-accept rates (and having > 10 answers) but we need the Posts with Self-accepted answers, not just the users.

SELECT TOP 100 
  Users.Id AS [User Link],
  (
    CAST(COUNT(a.Id) AS float) / 
    CAST(
      (
        SELECT COUNT(*) 
        FROM Posts p 
        WHERE p.OwnerUserId = Users.Id 
        AND PostTypeId = 1
      ) 
      AS float
    ) * 100
  ) AS SelfAnswerPercentage
FROM Posts q
  INNER JOIN Posts a ON q.AcceptedAnswerId = a.Id
  INNER JOIN Users ON Users.Id = q.OwnerUserId
WHERE q.OwnerUserId = a.OwnerUserId
GROUP BY Users.Id, DisplayName
HAVING COUNT(a.Id) > 10
ORDER BY SelfAnswerPercentage DESC

Answer 1

Can you post your expected output? And the schema of your tables?

I'm not familiar with your DB schema, but this could be a starting point:

SELECT q.*
FROM posts q -- Get all posts
INNER JOIN posts a ON q.AcceptedAnswerId = a.Id
  AND q.OwnerUserId = a.OwnerUserId AND a.PostTypeId = 2 -- Get accepted self-answers
INNER JOIN users u ON u.Id = q.OwnerUserId -- get OP's user info
WHERE q.PostTypeId = 1 -- Only get questions

I'm not sure where your comments & suggested edits are stored, but I'm guessing you will need to do some kind of group_concat() or JSON-type aggregation to return that data if you only want one row per question post.