What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 131 Stack Exchange communities.

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

2 Answers 2

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 
                      END 
    FROM   (SELECT COUNT(*) * 10      AS votes, 
                   votes.creationdate AS creationdate 
            FROM   posts 
                   INNER JOIN votes 
                     ON votes.postid = posts.id 
                        AND votetypeid = 2 
            WHERE  posts.owneruserid = @userid 
                   AND posts.communityowneddate IS NULL 
            GROUP  BY votes.creationdate 
            UNION 
            SELECT COUNT(*) * -2      AS votes, 
                   votes.creationdate AS creationdate 
            FROM   posts 
                   INNER JOIN votes 
                     ON votes.postid = posts.id 
                        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
2  
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:

DECLARE @UserID int
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

http://jcoehoorn.dyndns.org/stackql/default.aspx?qid=614

That gives you 37459 instead of your measly 24516

share|improve this answer
1  
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 .