Answer/Close/Delete Rate per Month

0

Please login or register to vote for this query.

(click on this box to dismiss)

The relative (rate) of questions being answered in 1/7/14 days or ever, as well as deletion and closure rates.

Craft CMS

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

--StartTime: Start time of plot
DECLARE @StartTime DATETIME = convert(DATETIME, '##StartTime?2000/01/01##')
DECLARE @EndTime DATETIME = dateadd(day,0,getdate())

select
  MonthOf,
  sum(cast(case when datediff(second, QDate, FADate) < 2592000 then 1 else 0 end as tinyint))/cast(count(*) as decimal) as '% A 30d',
  sum(cast(case when datediff(second, QDate, FADate) < 1209600 then 1 else 0 end as tinyint))/cast(count(*) as decimal) as '% A 14d',
  sum(cast(case when datediff(second, QDate, FADate) < 604800 then 1 else 0 end as tinyint))/cast(count(*) as decimal) as '% A 7d',
  sum(cast(case when datediff(second, QDate, FADate) < 86400 then 1 else 0 end as tinyint))/cast(count(*) as decimal) as '% A 1d',
  sum(cast(case when FADate is not null then 1 else 0 end as tinyint))/cast(count(*) as decimal) as '% A',
  sum(1-Clo)/cast(count(*) as decimal) as 'not closed',
  sum((1-Clo)*(1-Del))/cast(count(*) as decimal) as 'not clo/del'
from(select -- SUBQUERY: select question date, first answer date, deleted flag
    dateadd(day, 1-datepart(day,q.CreationDate), convert(date,q.CreationDate)) as 'MonthOf', -- Month
    q.CreationDate as 'QDate', -- Question date
    min(a.CreationDate) as 'FADate', -- First answer date
    cast(case when q.ClosedDate is not null then 1 else 0 end as tinyint) as 'Clo', -- Closed
    cast(case when q.DeletionDate is not null then 1 else 0 end as tinyint) as 'Del' -- Deleted
  from
    PostsWithDeleted as q left outer join Posts as a on a.ParentId = q.Id
  where
    q.CreationDate > @StartTime and q.CreationDate < @EndTime
    and q.PostTypeId = 1 -- questions only
    group by q.Id,q.CreationDate,q.ClosedDate,q.DeletionDate
    ) qa
group by MonthOf
order by MonthOf

Enter Parameters

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