answers per question over time

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

-- answers per question over time

select *, (answers + 0.0) / (questions + 0.0) [answer per question], votes / (answers + questions + 0.0) [votes per post]
from (
select start, 
       sum(case when PostTypeId = 1 then 1 else 0 end) questions,  
       sum(case when PostTypeId = 2 then 1 else 0 end) answers, 
       (
         select count(*) from Votes v 
join Posts p2 on p2.Id = v.PostId
where VoteTypeId in (2,3) 
and p2.CreationDate between start and finish  
and v.CreationDate between start and finish
and p2.CommunityOwnedDate is null
       ) as votes

from Posts p
join master.dbo.fnGetDateRanges('m','1-1-2009','1-1-2011') on 
  p.CreationDate between start and finish
where CommunityOwnedDate is null
group by start,finish
) as X
order by start

created feb 24 11