Plot the site activity by voting, questions, answers and comments, aggregated per week.
Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Japanese language
-- 2014-12-09 - exclude closed questions from charts. declare @epoch as datetime = DATEADD(WEEK, - ##Weeks:int?104## - 1, CURRENT_TIMESTAMP) ; with VoteNames as ( select pt.Id as PostTypeId, vt.Id as VoteTypeId, case when pt.Id in (1, 2) and vt.Id = 1 then 'Answer Accepts in week' when pt.Id = 1 and vt.Id != 1 then 'Question Votes in week' when pt.Id = 2 and vt.Id != 1 then 'Answer Votes in week' else 'Unknown' end as VName from VoteTypes vt, PostTypes pt where vt.Id in (1, 2, 3) and pt.Id in (1, 2) ) , UserAct as ( select distinct DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,CreationDate)), CONVERT(DATE,CreationDate)) as YearMon, UserId as UserId from PostHistory where CreationDate > @epoch UNION select distinct DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,CreationDate)), CONVERT(DATE,CreationDate)) as YearMon, UserId from Comments where CreationDate > @epoch ) , Activity as ( select YearMon, 'Active Users in Week' as Series, count(UserId) as Action from UserAct u group by YearMon UNION select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) as YearMon, 'New Users in week' as Series, count(*) as Action from Users u where u.CreationDate > @epoch group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) UNION select DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate) as YearMon, VName as Series, count(*) as Action from Posts p, Votes v, VoteNames as vn where p.Id = v.PostId and v.VoteTypeId = vn.VoteTypeId and p.PostTypeId = vn.PostTypeId and v.VoteTypeId in (1, 2, 3) and p.PostTypeId in (1, 2) and v.CreationDate > @epoch group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate), VName UNION select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)) as YearMon, case when pt.Id = 1 then 'Questions Asked in week' when pt.Id = 2 then 'Answers Given in week' else 'Unknown' end as Series, count(*) as Action from Posts p, PostTypes pt where p.PostTypeId = pt.Id and p.PostTypeId in (1, 2) and p.CreationDate > @epoch and p.ClosedDate is null Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)), pt.Id UNION SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate)) as YearMon, 'Comments in week' as Series, count(*) as Action from Comments c, Posts p where c.CreationDate > @epoch and c.PostId = p.Id and p.ClosedDate is null Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate)) ) select * from Activity pivot ( sum(Action) for Series in ( [Questions Asked in week], [Answer Accepts in week], [Answers Given in week], [Comments in week], [Question Votes in week], [Answer Votes in week], [New Users in week], [Active Users in Week])) as Pivoted where YearMon >= DATEADD(WEEK, - ##Weeks:int?60## , CURRENT_TIMESTAMP) -- previous 60 weeks. and YearMon < DateAdd(dd, -1, CURRENT_TIMESTAMP) -- ignore most recent week. order by YearMon