Time to Answer

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.

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

select TagName, 
       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, 
         format(q.CreationDate, 'yyyy-MM-dd') 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

group by
    TagName,
    datepart(year, Asked),
    datepart(month, Asked)
    
order by 
    TagName,
    datepart(year, Asked),
    datepart(month, Asked)

Enter Parameters

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