Q&A for SharePoint enthusiasts
-- Define the ChatGPT release date and extended periods for analysis DECLARE @ChatGPTReleaseDate DATE = '2022-11-30'; DECLARE @BeforeStart DATE = '2022-09-01'; DECLARE @BeforeEnd DATE = '2022-11-30'; DECLARE @AfterStart DATE = '2022-12-01'; DECLARE @AfterEnd DATE = '2023-02-28'; -- 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 @BeforeStart AND @AfterEnd GROUP BY p.OwnerUserId HAVING COUNT(p.Id) >= 2 ), -- Aggregate monthly post counts by user for before and after periods UserPostCounts AS ( SELECT u.OwnerUserId, CASE WHEN p.CreationDate BETWEEN @BeforeStart AND @BeforeEnd THEN 'Before ChatGPT Release' WHEN p.CreationDate BETWEEN @AfterStart AND @AfterEnd THEN 'After ChatGPT Release' END AS Period, COUNT(p.Id) AS TotalPosts 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 @BeforeStart AND @BeforeEnd OR p.CreationDate BETWEEN @AfterStart AND @AfterEnd) GROUP BY u.OwnerUserId, CASE WHEN p.CreationDate BETWEEN @BeforeStart AND @BeforeEnd THEN 'Before ChatGPT Release' WHEN p.CreationDate BETWEEN @AfterStart AND @AfterEnd THEN 'After ChatGPT Release' END ) -- Final selection with ratio calculation SELECT b.OwnerUserId, b.TotalPosts AS BeforeTotalPosts, a.TotalPosts AS AfterTotalPosts, CASE WHEN b.TotalPosts > 0 THEN CAST(a.TotalPosts AS FLOAT) / b.TotalPosts ELSE NULL END AS PostRatio FROM (SELECT OwnerUserId, TotalPosts FROM UserPostCounts WHERE Period = 'Before ChatGPT Release') b LEFT JOIN (SELECT OwnerUserId, TotalPosts FROM UserPostCounts WHERE Period = 'After ChatGPT Release') a ON b.OwnerUserId = a.OwnerUserId ORDER BY PostRatio DESC;