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.

Q&A for Ubuntu users and developers

WITH
    Answers AS (
        SELECT *
            FROM Posts
            WHERE PostTypeId = 2 -- Answers
    )
select
  -- Asked,
  Tags,
  --ViewCount,
  --avg(cast(ViewCount as float)) ViewCount,
  --avg(cast(AnswerCount as float)) AnswerCount,
  --avg(cast(CommentCount as float)) CommentCount,
  --avg(AnswerCount),
  --avg(CommentCount),
  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 
    a.Tags,
    --t.ViewCount,    
    --t.AnswerCount,
    --t.CommentCount,

    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 '2017' AND '2018' 
    and a.PostTypeId = 2
    --and a.PostTypeId = 1
    and a.Score >= 1
    and DATEPART(HOUR, AcceptedAnswer.CreationDate) between '8' AND '24'
    and datediff(second, q.CreationDate, AcceptedAnswer.CreationDate) < 25920 -- 30 days
  group by
    a.Tags,
    q.CreationDate
    --t.ViewCount,
    --t.AnswerCount,
    --t.CommentCount
    
) q
where
 Tags in (select Tags from Posts)
group by
  -- Asked,
  Tags
order by 
  2 desc
  -- Asked

fork query

edited mar 17 20 anonymous

created feb 23 14 retailcoder