Plot the site activity by voting, questions, answers and comments, aggregated per week.
プログラマーとプログラミングに熱心な人々のための質問と回答
with Activity as ( select DATEADD(DAY, 7 - DATEPART(WEEKDAY, u.CreationDate), u.CreationDate) as YearMon, 'NewUser' as Series, count (*) as Action from Users u group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, u.CreationDate), u.CreationDate) UNION ALL select DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate) as YearMon, case when p.PostTypeId = 1 then 'Q' else 'A' end + vt.Name as Series, count(*) as Action from Posts p, Votes v, VoteTypes vt where p.Id = v.PostId and v.VoteTypeId = vt.Id group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate), case when p.PostTypeId = 1 then 'Q' else 'A' end + vt.Name UNION ALL select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)) as YearMon, pt.Name as Series, count(*) as Action from Posts p, PostTypes pt where p.PostTypeId in (1, 2, -100) and p.PostTypeId = pt.Id Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)), pt.Name UNION ALL SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate)) as YearMon, 'Comment' as Series, count(*) as Action from Comments c Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate)) ) select * from Activity pivot ( sum(Action) for Series in ([NewUser], [Question],[Answer],[Comment],[QUpMod],[QDownMod],[QFavorite],[AUpMod],[ADownMod],[AAcceptedByOriginator])) as Pivoted where YearMon >= DATEADD(WEEK, -60, CURRENT_TIMESTAMP) -- previous 60 weeks. and YearMon < CURRENT_TIMESTAMP -- ignore most recent week. order by YearMon