Q&A for SharePoint enthusiasts
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 '2009-01-01' AND '2009-06-30' ) AND CreationDate <= (SELECT DATEADD(DAY, 31, MinDate) FROM MinCreationDate) AND YEAR(CreationDate) = 2009 -- 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