I am trying to gather information on the number of answers per question by week for a user with fewer than 25 reputation points from the math.stackexchange site (and the comment count per question).
I have the following query but it has not worked
-- 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
, avg(answercount) as [answercount]
from
(
--answercount from users with reputation < 25
select sdow
, count(*) as [answercount]
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 u.reputation < 25 -- rep
group by sdow
union
) as total