Percent lost to repcap and wikis among top 1000 users.

0

Please login or register to vote for this query.

(click on this box to dismiss)

Ranks the top 1000 users in decreasing order of greatest percent reputation lost to the reputation cap as well as community wikis.

Physics

Q&A for active researchers, academics and students of physics

-- % of current reputation lost to repcap among the top 1000 users.

declare @UncappedReputation table (id int, rep int, uncapped int)
insert @UncappedReputation
select top 1000
    id = Users.Id,
    rep = Users.Reputation,
-- Modified from: http://data.stackexchange.com/stackoverflow/query/15552/
    uncapped = SUM(CASE
        WHEN VoteTypeId = 1 THEN 15                    -- Accepted answer
        WHEN VoteTypeId = 2 AND PostTypeId = 1 THEN 5  -- Upvoted question
        WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10 -- Upvoted answer
        WHEN VoteTypeId = 3 THEN -2                    -- Downvote
        WHEN VoteTypeId = 9 THEN BountyAmount          -- Earned Bounty
    END)
from
    Votes
  INNER JOIN 
    Posts ON Posts.Id = Votes.PostId
  INNER JOIN
    Users ON Users.Id = Posts.OwnerUserId
group by
    Users.Id,
    Users.Reputation
order by
    Users.Reputation desc
    

select top 1000
    t.id as [User Link],
    t.rep as Reputation,
    t.uncapped as [Uncapped Reputation],
    t.uncapped - t.rep as Lost,
    100 * (t.uncapped - t.rep) / t.rep as [Percent Lost]
from
    @UncappedReputation t
order by
    [Percent Lost] DESC

Enter Parameters

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