0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for system and network administrators

-- http://blog.stackoverflow.com/2010/03/important-reputation-rule-changes/
declare @epoch datetime = '##epoch?2010-03-19##';

with sample_posts as (
select p.Id,
       p.CreationDate posted,
       case
         when p.CreationDate < @epoch then
              case 
                when PostTypeId = 1 then 'ten_question' 
                when PostTypeId = 2 then 'ten_answer' 
              end
         when p.CreationDate >= @epoch then
              case 
                when PostTypeId = 1 then 'five_question' 
                when PostTypeId = 2 then 'five_answer' 
              end
       end post_type
from PostsWithDeleted p
where PostTypeId in (1,2)
)

select --count(*),
       dateadd(week, datediff(week, @epoch, posted), @epoch) week,
       sum(case when post_type = 'ten_question' then 1.0 end)
      /sum(case when post_type like 'ten_%' then 1.0 end) ten_ratio,
       sum(case when post_type = 'five_question' then 1.0 end)
      /sum(case when post_type like 'five_%' then 1.0 end) five_ratio
from sample_posts
group by datediff(week, @epoch, posted)
having count(*) > 150
order by datediff(week, @epoch, posted)

fork query

edited may 20 16

created oct 28 14 Jon "not doe" Ericson