Time to Answer

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.

スタック・オーバーフローMeta

プログラマーと熱狂的なプログラマーのためのサイトに関する質問と回答

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 '16' AND '24'
    and datediff(second, q.CreationDate, AcceptedAnswer.CreationDate) < 2592000 -- 30 days
  group by
    t.TagName,
    q.CreationDate
) q
where
 TagName in (select TagName from Tags)
group by
  -- Asked,
  TagName
-- order by 
  -- Asked

Enter Parameters

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