Q&A for SharePoint enthusiasts
-- Define the ChatGPT release date DECLARE @ChatGPTReleaseDate DATE = '2022-11-30'; -- Calculate the date ranges for analysis DECLARE @BeforeStartDate DATE = DATEADD(MONTH, -2, @ChatGPTReleaseDate); DECLARE @BeforeEndDate DATE = DATEADD(MONTH, -1, @ChatGPTReleaseDate); DECLARE @AfterStartDate DATE = DATEADD(MONTH, 1, @ChatGPTReleaseDate); DECLARE @AfterEndDate DATE = DATEADD(MONTH, 2, @ChatGPTReleaseDate); -- Temporary table to hold user post counts around the ChatGPT release date WITH UserPostWindow AS ( SELECT p.OwnerUserId, COUNT(p.Id) AS PostCount FROM Posts p WHERE p.PostTypeId IN (1, 2) -- 1 for Questions, 2 for Answers AND p.CreationDate BETWEEN @BeforeEndDate AND @AfterStartDate GROUP BY p.OwnerUserId HAVING COUNT(p.Id) >= 2 ) -- Calculate monthly post count for the selected users before and after ChatGPT release SELECT u.OwnerUserId, FORMAT(p.CreationDate, 'yyyy-MM') AS Month, CASE WHEN p.CreationDate < @ChatGPTReleaseDate THEN 'Before ChatGPT Release' ELSE 'After ChatGPT Release' END AS Period, COUNT(p.Id) AS MonthlyPostCount FROM Posts p JOIN UserPostWindow u ON p.OwnerUserId = u.OwnerUserId WHERE p.PostTypeId IN (1, 2) -- 1 for Questions, 2 for Answers AND (p.CreationDate BETWEEN @BeforeStartDate AND @BeforeEndDate OR p.CreationDate BETWEEN @AfterStartDate AND @AfterEndDate) GROUP BY u.OwnerUserId, FORMAT(p.CreationDate, 'yyyy-MM'), CASE WHEN p.CreationDate < @ChatGPTReleaseDate THEN 'Before ChatGPT Release' ELSE 'After ChatGPT Release' END ORDER BY u.OwnerUserId, Period, Month;