2

## Please login or register to vote for this query.

(click on this box to dismiss)

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;

### Enter Parameters

Options:
-
Switch to meta site
Hold tight while we fetch your results
:records returned in :time ms:cached