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?