Distribution of avid users by reputation tier

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

Code Review

Q&A for peer programmer code reviews

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

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

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
        WHERE #ReputationThresholds.Graduated = ##graduated:int?0##
), 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;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results