Q&A for students, teachers, polyglots, and anyone interested in the techniques of second-language acquisition
declare @epoch as datetime = DATEADD(WEEK, - ##Weeks:int?104## - 1, CURRENT_TIMESTAMP);
with Activity as (
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'
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)),
select *, 70 as '10 Questions / day'
for Series in (
[Questions Asked in week],
[Answers Given in week]))
where YearMon >= DATEADD(WEEK, - ##Weeks:int?30## , CURRENT_TIMESTAMP) -- previous 60 weeks.
and YearMon < DateAdd(dd, -1, CURRENT_TIMESTAMP) -- ignore most recent week.
order by YearMon