new users

0

Please login or register to vote for this query.

(click on this box to dismiss)

Genealogy and Family History

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;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results