Statistics based on total Questions and Answers per Month for the last 12

1

Please login or register to vote for this query.

(click on this box to dismiss)

Averages for the last 12 months (in 30 day chunks)

Stack Overflow

Q&A for programmers

-- Statistics based on total Questions and Answers per Month for the last 12
-- Averages for the last 12 months (in 30 day chunks)

set nocount on

create table #ranges (Id int identity, [start] datetime, [finish] datetime)

insert #ranges
select top 12 null, null
from sysobjects

declare @oldestPost dateTime

select @oldestPost = CreationDate from Posts
where Id = (select max(p2.Id) from Posts p2)

-- look at 30 day chunks, so stats remain fairly accurate
-- (month will depend on days per month)

update #ranges
  set
   [start] = DateAdd(d, (0 - Id) * 30, @oldestPost),
   [finish] = DateAdd(d, (1 - Id) * 30, @oldestPost)



select start, 100.0*(select count(*) from Posts where ParentId is null
   and CreationDate between [start] and [finish] )/
    (select count(*) from Posts where ParentId is not null
   and CreationDate between [start] and [finish] ) as [Q/R],
    (select avg(1.0*AnswerCount) from Posts where ParentId is null
   and CreationDate between [start] and [finish] ) as [Avg A per Q],
    (select count(*) from Posts where ParentId is null
   and CreationDate between [start] and [finish]
   and AnswerCount=0 ) as [Zero-Answer Questions]
from #ranges

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached