0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for historians and history buffs

-- CTE with year, week and start of week dates
with yw as (
select distinct 
    year(creationdate) as y 
 , datepart(week, creationdate) as w
 -- http://stackoverflow.com/a/3936891/578411
 , cast(
     DATEADD(
          day, 
          1 - DATEPART(dw, creationdate), 
          creationdate) 
      as date
    ) as sdow
from posts
)

-- grouping over the union
select sdow
 , sum(questions) as [questions]
 , sum(duplicate) as [duplicate]
from 
(
-- questions from users < 25 rep
select sdow
   , count(*) as [questions]
   , 0 as [duplicate]
from posts p
inner join users u on u.id = p.owneruserid
right outer join yw 
 on yw.y = year(p.creationdate) 
and yw.w = datepart(week, p.creationdate) 
where p.posttypeid = 1 -- q 
and u.reputation < 25 -- rep
group by sdow
union 
-- duplicates on questions from users < 25 rep
select sdow
   , 0
   , count(*)
from posts p
inner join users u on u.id = p.owneruserid
inner join PostHistory ph on ph.postid = p.id 
right outer join yw 
 on yw.y = year(ph.creationdate) 
and yw.w = datepart(week, ph.creationdate) 
where p.posttypeid = 1 -- q
and u.reputation < 25 -- rep
and ph.PostHistoryTypeId = 10 -- closed
and ph.comment = 101 -- as duplicate
group by sdow
) as   total
group by sdow
order by sdow

fork query

created aug 10 20 anonymous