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 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;

Enter Parameters

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