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)

Mathematics

Q&A for people studying math at any level and professionals 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)

insert #ranges
select top 120 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)


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