Time to Answer 4c

0

Please login or register to vote for this query.

(click on this box to dismiss)

Breaks down average time to answer questions by tag, across time. Filters out unanswered and questions answered after 30 days.

Stack Overflow

Q&A for professional and enthusiast programmers

WITH
    Answers AS (
        SELECT *
            FROM Posts
            WHERE PostTypeId = 2 -- Answers
    )
select
  -- Asked,
  TagName,
  
count(*) Questions,
  avg(cast(SecondsToAnswer as float))/60.0/60.0 AvgHoursToAnswer,
  stdev(cast(SecondsToAnswer as float))/60.0/60.0 StdevHoursToAnswer,
  avg(cast(BodyLength as float)) AvgBodyOfQuestions 
from
(
  select 
    t.TagName,
    dateadd(d, - datepart(d, q.CreationDate) + 1, Convert(Date, q.CreationDate)) Asked, 
    min(AcceptedAnswer.CreationDate) AnswerDate,
    Len(max(q.Body)) BodyLength,
    datediff(second, q.CreationDate, min(AcceptedAnswer.CreationDate)) SecondsToAnswer
  from Posts q
    inner join PostTags pt on q.Id = pt.PostId
    inner join Tags t on pt.TagId = t.Id
    inner join Posts a on q.Id = a.ParentId
    
     LEFT OUTER JOIN Answers AS FirstAnswer
            ON FirstAnswer.ParentId = q.Id
        LEFT OUTER JOIN Answers AS AcceptedAnswer
            ON AcceptedAnswer.Id = q.AcceptedAnswerId
   
  where
    q.CreationDate between    '2018-06-01 00:00:00' AND '2019-06-1 00:00:00'
    and q.PostTypeId = 1
    and a.PostTypeId = 2
    and a.Score >= 1
    and DATEPART(HOUR, AcceptedAnswer.CreationDate) between '0' AND '7'
    and datediff(second, q.CreationDate, AcceptedAnswer.CreationDate) < 2592000 -- 30 days
  group by
    t.TagName,
    q.CreationDate
) q
where
 TagName in ('php')
 and BodyLength > 1682.9053159478435
group by
  -- Asked,
  TagName
-- order by 
  -- Asked

Enter Parameters

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