0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people who build and repair bicycles, people who train cycling, or commute on bicycles

WITH TempTable AS (
    SELECT 
        OwnerUserId,
        COUNT(CASE WHEN CreationDate BETWEEN '2022-09-04' AND '2022-11-27' THEN Id ELSE NULL END) AS CountPeriodA,
        COUNT(CASE WHEN CreationDate BETWEEN '2022-12-04' AND '2023-02-26' THEN Id ELSE NULL END) AS CountPeriodB
    FROM posts
    WHERE PostTypeId = 1
    GROUP BY OwnerUserId
    HAVING 
        COUNT(CASE WHEN CreationDate BETWEEN '2022-09-04' AND '2022-11-27' THEN Id ELSE NULL END) >= 1
        AND 
        COUNT(CASE WHEN CreationDate BETWEEN '2022-12-04' AND '2023-02-26' THEN Id ELSE NULL END) >= 1
),
UserActivity AS (
    SELECT
        OwnerUserId,
        COUNT(CASE WHEN PostTypeId = 1 AND CreationDate BETWEEN '2021-11-30' AND '2022-11-30' THEN Id ELSE NULL END) AS QuestionCount,
        COUNT(CASE WHEN PostTypeId = 2 AND CreationDate BETWEEN '2021-11-30' AND '2022-11-30' THEN Id ELSE NULL END) AS AnswerCount,
        COUNT(CASE WHEN CreationDate BETWEEN '2021-11-30' AND '2022-11-30' THEN Id ELSE NULL END) AS TotalActivity
    FROM posts
    GROUP BY OwnerUserId
),
ActivityWithRank AS (
    SELECT
        *,
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY TotalActivity) 
        OVER () AS ActivityThreshold
    FROM UserActivity
),
ActiveUsersInTop10Percent AS (
    SELECT
        t.OwnerUserId,
        awr.TotalActivity,
        awr.ActivityThreshold
    FROM TempTable t
    JOIN ActivityWithRank awr ON t.OwnerUserId = awr.OwnerUserId
    WHERE awr.TotalActivity >= awr.ActivityThreshold
)
SELECT * FROM ActiveUsersInTop10Percent;

view current version

edited dec 9

created dec 9 Bowen.Zhou