Q&A for anime and manga fans
-- 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 CreationDate BETWEEN '2009-01-01' AND '2009-12-31' ) AND CreationDate <= (SELECT DATEADD(DAY, 30, 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;