Answered (1/7/14/all days) vs. Closed/Deleted Questions on SO

0

Please login or register to vote for this query.

(click on this box to dismiss)

In contrast to earlier versions, this normalizes with the total count (including deleted questions, hence not effected by the garbage collector cronjob), making the chart more interpretable.

Stack Overflow

Q&A for professional and enthusiast programmers

--StartTime: Start time of plot
DECLARE @StartTime DATE = convert(DATE, '##StartTime?2000/01/01##')
DECLARE @EndTime DATE = convert(date,dateadd(day,-5-datepart(weekday,getdate()),getdate()))

select
  WeekOf,
  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, 2-datepart(weekday,q.CreationDate), convert(date,q.CreationDate)) as 'WeekOf', -- Week
    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 WeekOf
order by WeekOf

Enter Parameters

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