GINI Based on Reputation (All People)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Statistical Analysis

Q&A for people interested in statistics, machine learning, data analysis, data mining, and data visualization

-- This is an attempt to calculate GINI without dropping people with 
-- low reputations due to the 50,000 limit
-- CutOff can be negative (to make sure to grab everyone)

WITH Rankings AS (
  SELECT Reputation, 
  ROW_NUMBER() OVER(ORDER BY Reputation ASC) AS RankInt 
  FROM Users
  WHERE Reputation > ##CutOffReputation##
  )
, AggStats AS (
  SELECT Sum(Reputation) AS SumRep, COUNT(*) AS N_Peop 
  FROM Users 
  WHERE Reputation > ##CutOffReputation##
)
, GiniInd AS (
  SELECT 
  Reputation, 
  AVG( CAST(RankInt AS Float) ) AS MeanRank,
  COUNT(*) AS NUsers,
  MAX(SumRep) AS SumRepInd, MAX(N_Peop) AS N_PeopInd
  FROM Rankings
    CROSS JOIN AggStats
  GROUP BY Reputation
)
, GiniAgg AS (
SELECT 
  SUM( (N_PeopInd + 1 - MeanRank)*Reputation*NUsers ) AS Num,
  MAX( (N_PeopInd + 1)/CAST(N_PeopInd AS Float) ) AS One,
  MAX( CAST(N_PeopInd AS Float)*SumRepInd ) AS Den
  FROM GiniInd
  )
SELECT 
One - (2*Num)/Den AS Gini
FROM GiniAgg

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results