0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Exchange uses a query similar to this one to determine whether a site has healthy participation, with users who return to the site. This query disregards users with less than 150 reputation, as 150 is taken as the minimum to be considered an "avid user". http://chat.stackexchange.com/transcript/8595?m=12335181#12335181

Q&A about the site for music historians, critics, and fans

CREATE TABLE #ReputationThresholds
( Id             INTEGER
, ThresholdScore INTEGER
);

INSERT INTO #ReputationThresholds
    VALUES (1, 150), (2, 200), (3, 500), (4, 1000),
           (5, 2000), (6, 4000);

WITH ReputationTiers AS (
    SELECT #ReputationThresholds.Id
         , #ReputationThresholds.ThresholdScore
         , NextThreshold.ThresholdScore AS NextThresholdScore
        FROM #ReputationThresholds
            LEFT OUTER JOIN #ReputationThresholds AS NextThreshold
                ON NextThreshold.Id = #ReputationThresholds.Id + 1
), AvidUsers AS (
    SELECT Id, Reputation
        FROM Users
        WHERE Reputation >= (SELECT MIN(ThresholdScore) FROM ReputationTiers)
), UserCount AS (
    SELECT Count(Id) AS UserCount
        FROM AvidUsers
), ThresholdsCountsAndPercentages AS (
    SELECT ThresholdScore
         , Count(Users.Id) AS TierUserCount
         , CAST(Count(Users.Id) AS DECIMAL)/ UserCount AS TierUserPercentage
        FROM ReputationTiers
            INNER JOIN UserCount ON 1=1
            LEFT OUTER JOIN AvidUsers AS Users
                ON Reputation >= ThresholdScore
                AND (Reputation < NextThresholdScore OR NextThresholdScore IS NULL)
        GROUP BY ThresholdScore, UserCount
)
SELECT ThresholdScore AS [Reputation Threshold]
     , TierUserCount AS [# Users]
     , ROUND(100 * TierUserPercentage, 4) AS [% of Avid Users]
     , ROUND(100 * SUM(TierUserPercentage) OVER (ORDER BY ThresholdScore), 4) AS [Cumulative % of Avid Users]
    FROM ThresholdsCountsAndPercentages
    ORDER BY 1;

fork query

edited nov 5 anonymous

created jan 16 14 200_success