Q&A for professional and enthusiast programmers
-- Example for 6-12 months before/after ChatGPT release (apply similar changes to all queries) DECLARE @ChatGPTRelease DATE = '2022-11-30'; DECLARE @Period1Start DATE = DATEADD(MONTH, -13, @ChatGPTRelease); -- Extended by 1 month DECLARE @Period1End DATE = DATEADD(MONTH, -6, @ChatGPTRelease); DECLARE @Period2Start DATE = DATEADD(MONTH, 5, @ChatGPTRelease); -- Extended by 1 month DECLARE @Period2End DATE = DATEADD(MONTH, 12, @ChatGPTRelease); -- Use a temp table to store the monthly active user counts CREATE TABLE #MonthlyActiveUsers ( PeriodNumber INT, YearMonth NVARCHAR(7), ActiveUsers INT ); -- Insert data for Period 1 (6-12 months pre-ChatGPT) INSERT INTO #MonthlyActiveUsers (PeriodNumber, YearMonth, ActiveUsers) SELECT 1, FORMAT(CreationDate, 'yyyy-MM'), COUNT(DISTINCT OwnerUserId) * 10 -- Multiplying by 10 to estimate full count FROM Posts WHERE CreationDate BETWEEN @Period1Start AND @Period1End AND OwnerUserId IS NOT NULL AND OwnerUserId % 10 = 0 -- Sampling 10% of users AND PostTypeId IN (1, 2) -- Only questions and answers GROUP BY FORMAT(CreationDate, 'yyyy-MM'); -- Insert data for Period 2 (6-12 months post-ChatGPT) INSERT INTO #MonthlyActiveUsers (PeriodNumber, YearMonth, ActiveUsers) SELECT 2, FORMAT(CreationDate, 'yyyy-MM'), COUNT(DISTINCT OwnerUserId) * 10 -- Multiplying by 10 to estimate full count FROM Posts WHERE CreationDate BETWEEN @Period2Start AND @Period2End AND OwnerUserId IS NOT NULL AND OwnerUserId % 10 = 0 -- Sampling 10% of users AND PostTypeId IN (1, 2) -- Only questions and answers GROUP BY FORMAT(CreationDate, 'yyyy-MM'); -- Final select with row numbers for side-by-side comparison, excluding the first month of each period SELECT PeriodNumber, YearMonth, ActiveUsers, ROW_NUMBER() OVER (PARTITION BY PeriodNumber ORDER BY YearMonth) AS MonthNumber FROM #MonthlyActiveUsers WHERE YearMonth != (SELECT MIN(YearMonth) FROM #MonthlyActiveUsers WHERE PeriodNumber = 1) AND YearMonth != (SELECT MIN(YearMonth) FROM #MonthlyActiveUsers WHERE PeriodNumber = 2) ORDER BY PeriodNumber, YearMonth; -- Clean up DROP TABLE #MonthlyActiveUsers;