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 Tridion developers and administrators
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;