Q&A for expert genealogists and people interested in genealogy or family history
-- Create a temporary table to hold filtered posts CREATE TABLE #FilteredPosts ( OwnerUserId INT, CreationDate DATETIME, PostTypeId INT, Year INT, Week INT ); -- Insert filtered data into the temporary table INSERT INTO #FilteredPosts SELECT OwnerUserId, CreationDate, PostTypeId, YEAR(CreationDate) AS Year, DATEPART(WEEK, CreationDate) AS Week FROM Posts WHERE OwnerUserId IN ( SELECT Id FROM Users WHERE YEAR(CreationDate) = 2010 -- Specify year only ) AND CreationDate <= (SELECT DATEADD(DAY, 31, MIN(CreationDate)) FROM Posts); -- Now perform the aggregation from the temporary table SELECT Year, Week, 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 #FilteredPosts GROUP BY Year, Week ORDER BY Year, Week; -- Drop the temporary table DROP TABLE #FilteredPosts;