Average answer count per question and comment count per question by week

Question

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

Answer 1

Something like this. I've corrected the syntax errors, you have a union of something against nothing for instance.

I've also restricted the posts to questions since that seems to make more sense for your query (p.posttypeid = 1)

I've not done the comment count, maybe you can do that yourself or ask another question if not.

-- 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
)

--answercount from users with reputation < 25
select sdow
, avg(cast(p.answercount as float)) 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
and p.posttypeid = 1 -- questions
group by sdow

You can see it run here