DECLARE @ChatGPTReleaseDate DATE = '2022-11-30'; DECLARE...

0

Please login or register to vote for this query.

(click on this box to dismiss)

SharePoint

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;

Enter Parameters

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