4

Please login or register to vote for this query.

(click on this box to dismiss)

Generates a histogram of users' reputation. The histogram bins are automatically determined using Doane's formula, and the lowest bin is discarded since it would probably contain so many users that the rest of the histogram would be unreadable. Obviously best viewed as a graph.

Q&A for professional and enthusiast programmers

DECLARE @BinSize int;

-- 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
        FROM Users
        WHERE Reputation >= 150
), Mean AS (
    SELECT AVG(Reputation) AS μ
        FROM AvidUsers
), Moments AS (
    SELECT COUNT(Id) AS n
         , μ
         , STDEV(Reputation) AS σ
         , AVG(POWER(CAST(Reputation - μ 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 ROUND(MAX(Reputation) / k, -2) AS BinSize
        FROM AvidUsers, DoanesFormula
        GROUP BY k
) SELECT @BinSize = BinSize FROM BinSize;

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
), Bins AS (
    SELECT bin = @BinSize * (ROW_NUMBER() OVER (ORDER BY n) + 0.5)
        FROM e3
), UserBins AS (
    SELECT Reputation / @BinSize + 0.5 * @BinSize AS bin
        FROM Users
), UserCounts AS (
    SELECT bin AS Reputation
         , COUNT(*) OVER (PARTITION BY bin) AS Count
    FROM UserBins
)
SELECT Bins.bin AS Reputation
     , Reputation
    FROM Bins
        LEFT OUTER JOIN UserCounts
            ON Bins.bin = Reputation
    WHERE Bins.bin <= (SELECT MAX(Reputation) FROM Users)
    ORDER BY Bins.bin;

view current version

edited mar 23 14

created mar 23 14 200_success