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.

Q&A for professional and enthusiast 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
)
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
    ORDER BY Month;

fork query

edited jun 18 18 Athari

created jul 31 14 Loki Astari