Percent lost to reputation cap among top 1000 users.

4

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. (NOTE: This does not count deleted posts where rep is retained from being deleted at +3 and >60 days old. Furthermore, this does not take into account for downvotes cast.

English

Q&A for linguists, etymologists, and serious English language enthusiasts

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

declare @PostCounts table (id int, postcount int)
insert @PostCounts
select top 1000
    id = Users.Id,
    postcount = Count(Posts.id)
from
    Posts
  INNER JOIN
    Users ON Users.Id = Posts.OwnerUserId
group by
    Users.Id,
    Users.Reputation
order by
    Users.Reputation desc
    

declare @UncappedReputation table (id int, rep int, uncapped int)
insert @UncappedReputation
select top 1000
    id = Users.Id,
    rep = Users.Reputation,
    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
where Posts.CommunityOwnedDate is null
group by
    Users.Id,
    Users.Reputation
order by
    Users.Reputation desc
    

select top 1000
    t.id as [User Link],
    p.postcount as [Posts],
    t.rep as [Rep],
    t.uncapped as [Uncapped Rep],
    t.uncapped - t.rep as [Rep Lost],
    Cast(100. * (t.uncapped - t.rep) / t.rep AS DECIMAL(20, 3)) as [Percent Lost],
    Cast(1.0 * t.rep      / p.postcount AS DECIMAL(20, 3)) as [Rep / Post],
    Cast(1.0 * t.uncapped / p.postcount AS DECIMAL(20, 3)) as [Uncapped Rep / Post]
from @UncappedReputation t
  left outer join @PostCounts p
    on t.id = p.id
order by
    [Percent Lost] DESC

Enter Parameters

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