Reputation Visualizer

2

Please login or register to vote for this query.

(click on this box to dismiss)

Distribution of reputation among all users, as a percentage of total users. You can see some interesting trends by looking at the graph. The default parameters should make a great starting point, but if you want change them, be careful not to set the step-wise granularity too low; otherwise, the query will timeout before it finishes executing.

Amateur Radio

Q&A for amateur radio enthusiasts

-- Visualize how reputation is distributed among stackexchange users on a site.

-- Parameters --
----------------
-- Step: Step-wise granularity:
-- UpperLimit: Upper Limit (Users above this rep-threshould will be consolidated:
-- LowerLimit: Lower Limit (Users below this threshold will be ignored):
-- PrintLabel: Print a label column? (0 or 1)

-- Count the total number of users. This excludes users below
-- LowerLimit, to avoid infinitesmal percentatges. On Stack Overflow,
-- for instance 60% of users have only 1 rep, and only 8% have more than
-- 100 rep. Excluding lower-rep users from calculations can give clearer
-- results & a prettier graph
Declare @totalUserCount Decimal;
Select @totalUserCount = count(1) from USERS
    where Reputation >= ##LowerLimit:Int?100##;

-- Temp table to store the results
Declare @Results Table (
    Reputation_Cap Int,
    Label VarChar(20),
    Number_Of_Users Int
);

Declare @repCap Int;
Set @repCap = ##LowerLimit##;

-- For every Step between LowerLimit and UpperLimit, calculate how many
-- users have at least that much rep, but not as much as the next Step;
-- Store the results into the temp table
While (@repCap <= ##UpperLimit:int?1000##)
Begin
    Insert into @Results (Reputation_Cap, Label, Number_Of_Users)
        (Select @repCap,
                (Cast(@repCap As VarChar) + '-' + Cast(@repCap -1 + ##Step## As VarChar)),
                Count(1)
            From USERS
            Where Reputation >= @repCap
              and Reputation < @repCap + ##Step:int?100##
        )
    ;
        
    Set @repCap = @repCap + ##Step##;
End

-- Coalesce all remaining high-rep users into a singe row in the temp
-- table
Set @repCap = @repCap +1 - ##Step##;
Insert into @Results (Reputation_Cap, Label, Number_Of_Users)
        (Select @repCap + ##Step## * 2,     -- kluge, just to make the graph look okay
                (Cast(@repCap As VarChar) + ' and up'),
                Count(1)
            From USERS
            Where Reputation >= @repCap
        )
    ;

-- Print the result. Here we decide whether to add a label to the result set.
-- Doing so makes the results more readable, but munges the graph.
If (##PrintLabel:Int?0##  >= 1)
    Select Reputation_Cap As "Reputation",
           Label,
          (Number_Of_Users / @totalUserCount) * 100 as "Percentage of Users"
        From @Results;
Else
    Select Reputation_Cap As "Reputation",
          (Number_Of_Users / @totalUserCount) * 100 as "Percentage of Users"
        From @Results;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results