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.
Q&A about the site for serious players and enthusiasts of chess
-- % of current reputation lost to repcap among the top 1000 users. -- History: -- May 6, 2013: Optimized the query. -- Get Table of top 1000 users WITH userData(uid,rep,pcnt) AS( SElECT TOP 1000 Users.Id, Reputation, SUM(CASE WHEN PostTypeId = 1 THEN 1 WHEN PostTypeId = 2 THEN 1 END) FROM Posts INNER JOIN Users ON Users.Id = Posts.OwnerUserId GROUP BY Users.Id, Users.Reputation ORDER BY Reputation DESC ), -- Compute uncapped rep of all users repData(uid,uncapped) AS( SELECT Users.Id, -- COUNT(*), SUM(CASE WHEN VoteTypeId = 9 THEN BountyAmount -- Earned Bounty WHEN Votes.CreationDate > Posts.CommunityOwnedDate THEN 0 -- After Community Wiki 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 ELSE 0 END ) FROM Votes INNER JOIN Posts ON Posts.Id = Votes.PostId INNER JOIN Users ON Users.Id = Posts.OwnerUserId WHERE Posts.PostTypeId = 1 or PostTypeId = 2 GROUP BY Users.Id, Users.Reputation ) -- Join tables SELECT u.uid AS [User Link], u.pcnt AS [Posts], -- r.votes AS [Votes Received], u.rep AS [Rep], r.uncapped AS [Uncapped Rep], r.uncapped - u.rep AS [Rep Lost], Cast(100. * (r.uncapped - u.rep) / u.rep AS DECIMAL(20, 3)) AS [Percent Lost], Cast(1.0 * u.rep / u.pcnt AS DECIMAL(20, 3)) AS [Rep / Post], Cast(1.0 * r.uncapped / u.pcnt AS DECIMAL(20, 3)) AS [Uncapped Rep / Post] FROM userData u LEFT OUTER JOIN repData r ON u.uid = r.uid ORDER BY [Percent Lost] DESC