Percentage of open questions getting answers within two weeks, by quarter

0

Please login or register to vote for this query.

(click on this box to dismiss)

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
        and Answer.CreationDate < DATEADD(day, 14, Question.CreationDate)
    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