Question answer rates (7days/14days/total) per month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @StartTime DATETIME = convert(DATETIME, '##StartTime?2017/01/01 00:00:00##')
DECLARE @EndTime DATETIME = dateadd(day,-14,getdate())

select
  MonthOf,
  sum(Answered)/cast(count(*) as decimal) as 'RateAnswered',
  sum(Answered14Days)/cast(count(*) as decimal) as 'RateAnsweredIn14Days',
  sum(Answered7Days)/cast(count(*) as decimal) as 'RateAnsweredIn7Days'
from (select
    dateadd(day, 1-datepart(day, CreationDate), convert(date,CreationDate)) as 'MonthOf',
    -- find some answer within 14 days
    cast(case when exists (select * from Posts a where
      a.ParentId = q.Id
      and datediff(second, q.CreationDate, a.CreationDate) < 1209600 -- 14 days
      ) then 1 else 0 end as int) as 'Answered14Days',
    cast(case when exists (select * from Posts a where
      a.ParentId = q.Id
      and datediff(second, q.CreationDate, a.CreationDate) < 604800 -- 7 days
      ) then 1 else 0 end as int) as 'Answered7Days',
    cast(case when exists (select * from Posts a where
      a.ParentId = q.Id
      ) then 1 else 0 end as int) as 'Answered'
  from
    PostsWithDeleted q
  where
    q.CreationDate > @StartTime and q.CreationDate < @EndTime
    and q.PostTypeId = 1 -- questions only
    and q.ClosedDate is null -- not closed
) t
group by
  MonthOf
order by
  MonthOf desc

Enter Parameters

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