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.

Q&A about the site for software engineers working on automated testing, continuous delivery, service integration and monitoring, and building SDLC infrastructure

-- % 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

fork query

edited may 6 13

created nov 12 12 Mysticial