0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for professional and independent game developers

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
),
ActivityWithThreshold AS (
    SELECT
        OwnerUserId,
        TotalActivity,
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY TotalActivity) 
        OVER () AS ActivityThreshold
    FROM UserActivity
    WHERE TotalActivity > 0  -- 确保只计算有活跃度的用户
),
ActiveUsersInTop10Percent AS (
    SELECT
        t.OwnerUserId,
        awt.TotalActivity,
        awt.ActivityThreshold
    FROM TempTable t
    JOIN ActivityWithThreshold awt ON t.OwnerUserId = awt.OwnerUserId
    WHERE awt.TotalActivity >= awt.ActivityThreshold
)
SELECT * FROM ActiveUsersInTop10Percent;

view current version

edited dec 9

created dec 9 Bowen.Zhou