I am trying to gather information on
- the number of questions marked as "duplicate" per week for a user with fewer than 25 reputation points from the math.stackexchange site
- the average number of comments/answers per question for a user with fewer than 25 reputation points
For the first part, I have
-- 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 CloseReasonTypes crt right outer join yw on yw.y = year(v.creationdate) and yw.w = datepart(week, v.creationdate) where p.posttypeid = 1 -- q and u.reputation < 25 -- rep and crt.closereasontypeid = 101 group by sdow ) as total group by sdow order by sdow
Unfortunately, the second part for the duplicates on questions is not compiling and I suspect that the problem is from
inner join users u on u.id = p.owneruserid inner join CloseReasonTypes crt
I am trying to use a similar approach for the second part (average number of comments/answers) as well.
Is there a quick fix?