Activity over time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Theoretical Computer Science

Q&A for theoretical computer scientists and researchers in related fields

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

-- Total Questions and Answers per Month for the last 12
-- Total number of questions and answers for the last 12 months (in 30 day chunks)


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)

create table #postUpvotes (PostId int, [CreationDate] datetime, [Upvotes] int)

insert #postUpvotes
select Posts.Id, max(Posts.CreationDate), count(*) from Posts INNER JOIN Votes on Posts.Id = Votes.PostID
and VoteTypeId = 2
Where DateDiff(day,Posts.CreationDate,Votes.CreationDate) <= ##upvoteWindow##
group by Posts.Id

select start, 
   (select count(*) from Posts where ParentId is null 
     and CreationDate between [start] and [finish] ) as [# Questions. (Time is 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],
  (select count(*) from #postUpvotes 
   where Upvotes >= 5 and
   CreationDate between [start] and [finish] ) as [# questions + answers recieving >= 5 upvotes within ##upvoteWindow## days],
  (select count(*) from #postUpvotes 
   where Upvotes >= 10 and
   CreationDate between [start] and [finish] ) as [# queestions + answers receiving >= 10 upvotes within ##upvoteWindow## days]
from #ranges
order by start;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results