Q&A for users of TeX, LaTeX, ConTeXt, and related typesetting systems
DECLARE @BinSize REAL; DECLARE @NBins INTEGER; -- After discarding users with < 150 reputation as outliers, calculate -- an optimal number of histogram bins by applying Doane's Formula. -- https://en.wikipedia.org/wiki/Histogram#Number_of_bins_and_width WITH AvidUsers AS ( SELECT Id , Reputation , LOG10(Reputation) AS Log10Reputation FROM Users WHERE Reputation >= 150 ), Mean AS ( SELECT AVG(Log10Reputation) AS μ FROM AvidUsers ), Moments AS ( SELECT COUNT(Id) AS n , μ , STDEV(Log10Reputation) AS σ , AVG(POWER(CAST(Log10Reputation - μ AS FLOAT), 4)) AS μ4 FROM AvidUsers, Mean GROUP BY μ ), Kurtosis AS ( SELECT n , μ4 / POWER(σ, 4) - 3 AS ɣ2 FROM Moments ), DoanesFormula AS ( SELECT CEILING(1 + LOG(n, EXP(1)) + LOG(1 + ɣ2 * SQRT(n / 6), EXP(1))) AS k FROM Kurtosis ), BinSize AS ( SELECT MAX(Log10Reputation) / k AS BinSize , k FROM AvidUsers, DoanesFormula GROUP BY k ) SELECT @BinSize = BinSize, @NBins = k FROM BinSize; -- Be sure to plot the histogram bins with 0 members as well. -- e3 is just some arbitrary table with 27 rows. WITH e1(n) AS ( -- 3 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), e2(n) AS ( -- 9 SELECT a.* FROM e1 AS a CROSS JOIN e1 AS b ), e3(n) AS ( -- 81 SELECT a.* FROM e2 AS a CROSS JOIN e2 AS b ), e4(n) AS ( -- @NBins SELECT ROW_NUMBER() OVER (ORDER BY n) FROM e3 ), Bins AS ( SELECT POWER(10.0, @BinSize * n) AS Lb , @BinSize * (n + 0.5) AS Bin , POWER(10.0, @BinSize * (n + 1)) AS Ub FROM e4 WHERE n <= @NBins + 1 AND POWER(10.0, @BinSize * (n + 1)) > 150 ), AvidUsers AS ( SELECT Id , Reputation , LOG10(Reputation) AS Log10Reputation FROM Users ) SELECT Bin AS Log10Reputation , LOG10(COUNT(*)) AS Log10Count FROM Bins LEFT OUTER JOIN AvidUsers ON Lb <= Reputation AND Reputation < Ub GROUP BY Bin ORDER BY Bin;