Distribution of avid users by reputation tier

1

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

declare @activedate as datetime = DateAdd (month, - ##SeenInMonths:int?6##, CURRENT_TIMESTAMP)

;

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

CREATE TABLE #ActiveUsers (
  ActiveId INTEGER
);

print 'Active date is ' + Convert(Varchar(30), @activedate)

insert into #ActiveUsers
    select distinct OwnerUserId
    from Posts
    where CreationDate > @activedate
  union
    select distinct UserId
    from Comments
    where CreationDate > @activedate
      

INSERT INTO #ReputationThresholds
    VALUES (1, 150), (2, 200), (3, 500), (4, 1000),
    (5, 2000), (6, 4000), (7, 5000), (8, 10000), (9, 15000), (10, 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
), AvidUsers AS (
    SELECT Id, Reputation
        FROM Users, #ActiveUsers
        WHERE Reputation >= (SELECT MIN(ThresholdScore) FROM ReputationTiers)
          and Id = ActiveId
), 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