# of active users prior to and after chatgpt

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

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;

Enter Parameters

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