Percent lost to reputation cap 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. (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.

Music Fans Meta

Q&A about the site for music historians, critics, and fans

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

-- History:
--     May 6, 2013: Optimized the query.
--     Oct 15, 2020: Changed Question Upvote value to 10
--     January 15, 2021: Added correction for bounties offered.


-- 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 = 8 THEN -(BountyAmount)                     -- Awarded Bounty
        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 10             -- 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

Enter Parameters

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