Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 157 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

Just out of curiosity, is there some sort of tool, script or stored procedure that can use the monthly dump to calculate SO members' reputation as if there were no reputation cap?

I guess this is extremely hard to do, as it would effectively involve rebuilding the entire rep calculation process, but you never know.

My bet is that in the upper field (10-20k+), there will be extremely increased reputation numbers but roughly the same ranking order of users.

share|improve this question
I just tried making something on StackQL, but the server is bogging down joining the Posts and Votes tables... ugh... maybe if I get a local copy working this weekend. – Jon Seigel Mar 13 '10 at 0:03
Cheers @Jon, Joel already has what looks like a workable solution below. – Pëkka Mar 13 '10 at 0:21
up vote 4 down vote accepted

Joel's query doesn't look for bounties and also doesn't count reputation which has been received before a post went into community wiki mode. I've found a nice query which looks pretty good and which should be correct. For example, Jon Skeet lost 816,430 reputation just due to reputation cap. If I would run Joel's query, it would show that Jon skeet lost a total of 1,212,401 which is REALLY inaccurate.

Code inserted by Pekka

-- Reputation cap cut off
-- This query calculates how much reputation has been cut off because
-- you reached the limit of 200 reputation per day (through upvotes).
DECLARE @UserId int = ##UserId##
SELECT SUM(overvoted.sumvotes) 
FROM   (SELECT sumvotes = CASE 
                        WHEN SUM(allvotes.votes) >= 200 THEN 
                        SUM(allvotes.votes) - 200 
                        ELSE 0 
    FROM   (SELECT COUNT(*) * 10      AS votes, 
                   votes.creationdate AS creationdate 
            FROM   posts 
                   INNER JOIN votes 
                     ON votes.postid = 
                        AND votetypeid = 2 
            WHERE  posts.owneruserid = @userid 
                   AND posts.communityowneddate IS NULL 
            GROUP  BY votes.creationdate 
            SELECT COUNT(*) * -2      AS votes, 
                   votes.creationdate AS creationdate 
            FROM   posts 
                   INNER JOIN votes 
                     ON votes.postid = 
                        AND votetypeid = 3 
            WHERE  posts.owneruserid = @userid 
                   AND posts.communityowneddate IS NULL 
            GROUP  BY votes.creationdate) AS allvotes 
    GROUP  BY allvotes.creationdate) AS overvoted 
share|improve this answer
That's a tax burden of about 66%. That's almost socialism. Intolerable. – Pëkka Feb 28 '12 at 18:37

The are a few missing pieces, like downvotes issued by the user and offensive votes. But it's possible to get an approximation that's probably closer than your current score. Here's one that ignores bounties:

SET @UserID = 187606

SELECT SUM(Case When VoteTypeID = 1 Then 15 WHEN VoteTypeID=2 THEN 10 WHEN VoteTypeID=3 THEN -2 ELSE 0 END)
FROM Votes v
INNER JOIN Posts p ON p.ID = v.PostID
WHERE p.OwnerUserID = @UserID AND p.CommunityOwnedDate IS NULL

That gives you 37459 instead of your measly 24516

share|improve this answer
Cheers @Joel, great stuff! According to that logic, Jon Skeet would be at 427235. Go figure. – Pëkka Mar 13 '10 at 0:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .