Average time to first answer (even more parameterized and precise)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Rewrite of http://data.stackexchange.com/codereview/query/211467/average-time-to-in-seconds-to-answer-a-question-by-language-tag in response to http://codereview.stackexchange.com/questions/58668/average-time-to-first-answer. See http://codereview.stackexchange.com/a/58679/9357 for an explanation.

Stack Overflow

Q&A for programmers

WITH FirstAnswers AS (
    SELECT Q.Id
         , Q.CreationDate AS QuestionTime
         , min(A.CreationDate) AS AnswerTime
        FROM Posts AS Q
            LEFT JOIN Posts AS A
               ON Q.Id = A.ParentId
            INNER JOIN PostTags on Q.Id = PostTags.PostId
            INNER JOIN Tags on Tags.Id = PostTags.TagId
        WHERE Tags.TagName in ('##TagName##')
        GROUP BY Q.Id, Q.CreationDate
), AnswerTime AS (
    SELECT Id
         , QuestionTime
         , AnswerTime
         , datediff(second, QuestionTime, AnswerTime) / 3600.0 AS ElapsedHours
         , dateadd(m, datediff(m, 0, QuestionTime), 0) AS Month
        FROM FirstAnswers
        WHERE datediff(second, QuestionTime, AnswerTime) / (3600.0*24.0) < ##MaxDays:float?7.0## OR AnswerTime is NULL
)
SELECT Month
     /*, count(QuestionTime) AS [Question count]
     , count(AnswerTime) as [Answered questions]*/
     , (cast(count(AnswerTime) as float) / cast(count(QuestionTime) as float)) as [Answer rate]
     , avg(ElapsedHours) AS [Average time to first answer (Hours)]
    FROM AnswerTime
    GROUP BY Month
    HAVING MONTH >= ##MinDate:string?20000101##
    ORDER BY Month;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results