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
            INNER JOIN Posts AS A
               ON Q.Id = A.ParentId
        WHERE EXISTS (
                SELECT PostId
                    FROM PostTags
                        INNER JOIN Tags
                            ON TagId = Tags.Id
                    WHERE PostId = Q.Id AND TagName = ##Tag:string?php##
            )
        GROUP BY Q.Id, Q.CreationDate
), AnswerTime AS (
    SELECT Id
         , QuestionTime
         , AnswerTime
         , datediff(second, QuestionTime, AnswerTime) / 86400.0 AS ElapsedDays
         , dateadd(m, datediff(m, 0, QuestionTime), 0) AS Month
        FROM FirstAnswers
)
SELECT Month
     , count(QuestionTime) AS [Question count]
     , avg(ElapsedDays) AS [Average time to first answer (Days)]
    FROM AnswerTime
    GROUP BY Month
    ORDER BY Month;

view current version

edited jul 31 14 200_success

created jul 31 14 Loki Astari