Number of questions, answers, and upvotes over time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Number of questions, answers, and upvotes/20 over time

MathOverflow

Q&A for professional mathematicians

-- adapted from Sam Saffron's query 485296 (Total Questions and Answers per Month for the last 12)

set nocount on 

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

declare @i int
declare @rows_to_insert int

set @i = 0
set @rows_to_insert = ##points##
while @i < @rows_to_insert
    begin
    INSERT INTO #ranges ([start], [finish]) VALUES (null, null)
    set @i = @i + 1
    end
    
declare @oldestPost dateTime

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

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

select start, 
   (select count(*) from Posts where ParentId is null 
     and CreationDate between [start] and [finish] ) as [# Questions (grouped into ##period##-day blocks)],
  (select count(*) from Posts where ParentId is not null 
     and CreationDate between [start] and [finish] ) as [# Answers],
  (select count(*)/20 From Votes
     where CreationDate between [start] and [finish] ) 
   as [# Upvotes / 20]

from #ranges

Enter Parameters

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