1

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 about the site for software quality control experts, automation engineers, and software testers

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
     , (avg((ElapsedHours))) AS [Average time to first answer (Hours)]
     -- , count(*) c
    FROM AnswerTime
    WHERE ElapsedHours > 0
    GROUP BY Month
    HAVING MONTH >= ##MinDate:string?20000101##
    ORDER BY Month DESC;

view current version

edited jun 15 21 Felipe.Hoffa35541

created jul 31 14 Loki Astari