Total "quickly high scoring" Q&A per month for last 12

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 #postUpvotes 
--   where CreationDate between [start] and [finish] ) as [Questions and Answers],
  (select count(*) from #postUpvotes 
   where Upvotes >= 5 and
   CreationDate between [start] and [finish] ) as [Q & A with >= 5 upvotes],
  (select count(*) from #postUpvotes 
   where Upvotes >= 10 and
   CreationDate between [start] and [finish] ) as [Q & A with >= 10 upvotes]
from #ranges
order by start;

Enter Parameters

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