What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 133 Stack Exchange communities.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
Thanks for the answer, any chance you have the SQL you use to do this? –  xiaohouzi79 Jan 21 '11 at 0:51
    
@xiaohouzi79: I don't have the SQL, but you can get the total unanswered from the API. api.stackoverflow.com/1.0/stats (API doc: api.stackoverflow.com/1.0/help) –  Bill the Lizard Jan 21 '11 at 1:07

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.

share|improve this answer
    
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. –  xiaohouzi79 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. –  xiaohouzi79 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 .