AWS Elemental first response

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

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 (
'okta')
        GROUP BY Q.Id, Q.CreationDate
), AnswerTime AS (
    SELECT Id
         , QuestionTime
         , AnswerTime
         , datediff(second, QuestionTime, AnswerTime) / 86400.0 AS ElapsedDays
         , YEAR(QuestionTime) AS Year
        FROM FirstAnswers
)
SELECT Year
     , count(QuestionTime) AS [Question count]
     , count(AnswerTime) as [Answered questions]
     , (cast(count(AnswerTime) as float) / cast(count(QuestionTime) as float)) as [Answer rate]
     , avg(ElapsedDays) AS [Average time to first answer (Days)]
     , sum(case when ElapsedDays < 1.0 then 1 else 0 end) as [Same Day]
     , cast(sum(case when ElapsedDays < 1.0 then 1 else 0 end) as float) / cast(count(QuestionTime) as float) as [Percent Same Day]
     , sum(case when ElapsedDays < 2.0 then 1 else 0 end) as [Next Day]
     , cast(sum(case when ElapsedDays < 2.0 then 1 else 0 end) as float) / cast(count(QuestionTime) as float) as [Percent Next Day]

     , sum(case when ElapsedDays < 7.0 then 1 else 0 end) as [Same Week]
    , cast(sum(case when ElapsedDays < 7.0 then 1 else 0 end) as float) / cast(count(QuestionTime) as float) as [Percent Same Week]

    FROM AnswerTime
    GROUP BY Year
    ORDER BY Year;

Enter Parameters

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