2

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 amateur radio enthusiasts

select 
    
       cast(format(Asked, 'yyyy-MM') as varchar(7)) MonthAsked,
       datepart(year, Asked) YearAsked,
--       datepart(month, Asked) MonthAsked,
--       count(*) Questions,
--       avg(SecondsToAnswer) AvgSecondsToAnswer,
--       avg(cast(SecondsToAnswer as float))/60.0 AvgMinutesToAnswer,
       avg(cast(SecondsToAnswer as float))/60.0/60 AvgHoursToAnswer
--       avg(cast(SecondsToAnswer as float))/60.0/60/24 AvgDaysToAnswer
from
(
  select t.TagName, 
         q.CreationDate Asked, 
         min(a.CreationDate) AnswerDate,
         datediff(second, q.CreationDate, min(a.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
  where
    q.PostTypeId = 1
    and a.PostTypeId = 2
  group by
    t.TagName, q.CreationDate
  having
    datediff(second, q.CreationDate, min(a.CreationDate)) < 2592000 -- 30 days
) q
where
    TagName = 'c#'
group by
    TagName,
    Asked
--    datepart(year, Asked),
--    datepart(month, Asked)
    
order by 
    TagName,
Asked
--    datepart(year, Asked),
--    datepart(month, Asked)

view current version

edited feb 23 14

created feb 23 14 retailcoder