0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Define the periods: 6 months before and after ChatGPT release
DECLARE @ChatGPTRelease DATE = '2022-11-30';
DECLARE @Period1Start DATE = DATEADD(MONTH, -6, @ChatGPTRelease);
DECLARE @Period1End DATE = DATEADD(DAY, -1, @ChatGPTRelease);
DECLARE @Period2Start DATE = @ChatGPTRelease;
DECLARE @Period2End DATE = DATEADD(MONTH, 6, @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 (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 (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
SELECT 
    PeriodNumber,
    YearMonth,
    ActiveUsers,
    ROW_NUMBER() OVER (PARTITION BY PeriodNumber ORDER BY YearMonth) AS MonthNumber
FROM #MonthlyActiveUsers
ORDER BY PeriodNumber, YearMonth;

-- Clean up
DROP TABLE #MonthlyActiveUsers;

fork query

created oct 15 jon.doe32071