2

How is the 82% of questions answered for Stack Overflow as listed on this page calculated?

I thought it would be (Number of posts with an answer) / (Number of posts total)

Number of posts with an answer:

SELECT COUNT(*) FROM Posts WHERE AnswerCount >= 1 AND PostTypeId = 1

Number posts total:

SELECT COUNT(*) FROM Posts WHERE PostTypeId = 1

But the answer I received is off by 10% compared to what is displayed. I'm assuming I've misread the schema.

3

An answer has to be either upvoted or accepted in order for the question to count as "Answered".

1

This is just to expand on Bill's answer:

NOTE: My understanding is not the sum value of votes, just any answer with at least 1 upvote.

Posts that have at least one answer with an Upvote:

WITH posts_with_votes(ID) AS (
   SELECT p.ID
   FROM Posts p
      JOIN Posts aa ON aa.ParentId = p.Id
      JOIN Votes v ON v.PostId = aa.Id
   WHERE aa.PostTypeId = 2
    AND p.PostTypeId = 1
    AND v.VoteTypeId = 2
   GROUP BY p.Id
)
SELECT COUNT(*) FROM posts_with_votes

Result: 884338

Posts that have at least one accepted answer that doesn't fall into the above group:

WITH posts_with_votes(ID) AS (
   SELECT p.ID
   FROM Posts p
      JOIN Posts aa ON aa.ParentId = p.Id
      JOIN Votes v ON v.PostId = aa.Id
   WHERE aa.PostTypeId = 2
    AND p.PostTypeId = 1
    AND v.VoteTypeId = 2
   GROUP BY p.Id
)
SELECT COUNT(*)
FROM Posts
WHERE AcceptedAnswerId IS NOT NULL
    AND ID NOT IN (SELECT ID FROM posts_with_votes)
    AND Posts.PostTypeId = 1

Result: 98754

884338 + 98754 = 983092

983092 / 1207634 = 81.4%

Apologies for the scrappy SQL.

  • Feel free to indicate if this incorrect.. or if you have a nicer way to get this. I just wanted to get the quickest answer to my problem. There's also a limit to what you can do in terms of results and processing time on the Data Explorer, so if you have a better solution it must work using the Data Explorer. – going Jan 21 '11 at 0:50
  • This is really close to the live numbers from the API and to what's displayed on the site at stackoverflow.com/unanswered/tagged/?tab=votes – Bill the Lizard Jan 21 '11 at 1:08
  • @Bill - Thanks for that. I guess the minor difference in numbers is only due to the data in the data explorer being a few days older. – going Jan 21 '11 at 1:18
  • Yeah, that's what I thought. The API data is much fresher than the data dump, but not quite as fresh as the site. – Bill the Lizard Jan 21 '11 at 1:22

You must log in to answer this question.

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