Q&A about the site for enthusiasts and scholars of mythology and folklore
WITH MinCreationDate AS ( SELECT MIN(CreationDate) AS MinDate FROM Posts ) SELECT YEAR(CreationDate) AS Year, -- Extract year from CreationDate DATEPART(WEEK, CreationDate) AS Week, -- Extract week number from CreationDate SUM(CASE WHEN PostTypeId = 1 THEN 1 ELSE 0 END) AS TotalQuestionsCount, SUM(CASE WHEN PostTypeId = 2 THEN 1 ELSE 0 END) AS TotalAnswersCount FROM Posts WHERE OwnerUserId IN ( SELECT Id FROM Users WHERE CreationDate BETWEEN '2008-01-01' AND '2008-12-31' ) AND CreationDate <= (SELECT DATEADD(DAY, 30, MinDate) FROM MinCreationDate) AND YEAR(CreationDate) = 2008 -- Change this for each batch (2023, 2022, etc.) GROUP BY YEAR(CreationDate), DATEPART(WEEK, CreationDate) -- Group by year and week ORDER BY Year, Week; -- Order by year and week