Percentage of open questions with answers, by quarter

0

Please login or register to vote for this query.

(click on this box to dismiss)

Number of open questions, percentage that have at least one answer, and the percentage that have at least one *good* answer (Score > 0). Question scores are ignored.

Stack Overflow

Q&A for programmers

with quarters as (
    SELECT
        DATEPART(yyyy, Question.CreationDate) as Year,
        DATEPART(q, Question.CreationDate) as Quarter,
        COUNT(*) as Total,
        SUM(CASE when Question.AnswerCount > 0 then 1 else 0 END) as Answered,
        SUM(CASE when Answer.Id is not null then 1 else 0 end) as GoodAnswer
    FROM Posts as Question
    LEFT JOIN Posts as Answer on Answer.PostTypeId=2 and Answer.ParentId=Question.Id and Answer.Score > 0
    WHERE Question.PostTypeId=1 AND Question.ClosedDate is null
    GROUP BY DATEPART(yyyy, Question.CreationDate),DATEPART(q, Question.CreationDate)
)

select
    Year,
    Quarter,
    Total as Questions,
    CAST(convert(numeric(5,2), cast(Answered as float) * 100 / Total) as VARCHAR(10)) + '%' as [Answered],
    CAST(convert(numeric(5,2), cast(GoodAnswer as float) * 100 / Total) as VARCHAR(10)) + '%' as [GoodAnswer]
from quarters
order by Year, Quarter

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached