I want to implement something similar to Stack Exchange's reputation system. What would be the better practice: using an SQL aggregate function to recalculate every time it changes, or storing reputations in a table and manually altering it every time reputation goes up or comes down? How does Stack Exchange's implementation work?

  • 40
    Haaaaaaaaaaaaaaaaa, you don't want to know. Mar 26, 2012 at 13:10
  • 15
    hehe I can almost sense @Nick starting to froth at the mouth (meaning: just when he gets this "done", it comes back to remind him) Mar 26, 2012 at 13:12
  • 3
    @chi - in case you missed that -you just received replies from not one but two SE developers :P
    – Lix
    Mar 26, 2012 at 13:15
  • 26
    Indeed it's a manual method. @NickCraver sits there with a calculator at midnight to re-calc peoples reputation.
    – Matt
    Mar 26, 2012 at 13:17
  • 4
    calculator?? HA! I heard they use an abacus for the global recalc...
    – Lix
    Mar 26, 2012 at 13:18
  • @Matt: I thought they were training hamsters to do that for them...doesn't go that well, huh? Mar 26, 2012 at 13:20
  • I really want to know, although I've implemented it using manual method, but I want to know the best practice. Thanks.
    – Chibuzo
    Mar 26, 2012 at 13:21
  • 2
    You've implemented it? Is there something you want to tell us? Mar 26, 2012 at 13:23
  • @Chibuzo manual method?? How does one do that?!
    – Naftali
    Mar 26, 2012 at 13:24
  • @Matt - And since it's a solar calculator ("going green" and all that), he has to work really fast.
    – cdeszaq
    Mar 26, 2012 at 13:27
  • 1
    I'm not working on a question and answer site like stackoverflow, I give points to users on each file upload, article post etc. I have a field on each users row in the database, I'll manually increment the users points/reputation when the user makes an upload or post, and will subtract if the user deletes.
    – Chibuzo
    Mar 26, 2012 at 13:33
  • Relevant comment as well.. Mar 26, 2012 at 14:01

1 Answer 1


So do we do this in SQL? Well, not really. We query to see if you're at the daily rep cap via SQL and make some decisions based on that, but we don't determine what reputation value you should have from that...it'd be a little crazy. If you want a detailed answer, keep reading.

First, credit goes to the entire team here, I didn't write the original rep code, or even all of the current code. The last major revision was from SuperDalgas and I, but there are a lot of hands that go into making this system tick.

It's a somewhat complicated question. First, let's visit how a rep recalc works.

We grab everything involved in your history and crawl over it doing a variety of things:

  • totalling the votes
    • gather time-based aggregates (rep this week, month, etc.)
  • fixing vote rep changes
    • for the rep cap
    • for the rep floor
    • other changes like merged users, etc.
  • see if you should be earning the association bonus, and grant it if missing

That's just the votes, we run over them and figure out what should be what, what totals each day has, etc. Now the way we store votes has 4 vital components (with regards to rep):

  • Voter
  • VoterRepChange (e.g. -1 on an answer downvote, or -400 for a bounty, +2 for accept, etc.)
  • Target
  • TargetRepChange

While this is great for storing the votes in a concise way, it suuuuuuuucks for determining reputation, because you have to do a Union of several sets of events (e.g. a Post deleted here counts but not here, unless it's a spam/offensive vote which always counts, etc.)

Once the votes are totaled up then we need to record a denormalized history of everything that affects a user:

  • Asked Accepts Answer
  • Asked Unaccepts Answer
  • Answer Accepted
  • Answer Unccepted
  • Voter Downvotes
  • Voter Undownvotes
  • Post Downvoted
  • Post Undownvoted
  • Post Upvoted
  • Post Unupvoted
  • Reveived Suggested Edit Approval
  • Spam Flag
  • Offensive Flag
  • Bounty Given
  • Bounty Earned
  • Bounty Cancelled
  • Post Deleted
  • Post Undeleted (not used, in practice, because a recalc happens)
  • Association Bonus
  • Arbitrary Reputation Change (StackApps API beta participation badge)
  • Vote Fraud Reversal
  • Post Migrated (not used in the DB, but shown in the UI, for a few reasons)
  • User Deleted

These are generated by looking at the results of the votes from the previous step, and recorded in order (chronologically) for the user as a ledger of all events affecting their rep (these RepHistory entries are what's shown on the reputation tab in the profile).

All of the above is for a recalc only. The process itself is atomic and non-commit-dependent. We track all of this through in-memory objects so we can fully simulate a recalc and all fixed votes without actually running it, this helps immensely with debugging (I highly recommend this for anyone considering a reputation system - I have built several developer debug views just for this purpose).

Then, there's the vast majority of reputation change cases, a standard delta:

When you do anything that changes your rep, or someone else does it, we need to evaluate what's happened. A variety of things may happen at this point to determine the delta and record history, the simplest example is an upvote on a non-capped user:

  1. User upvotes your non-CW post
  2. Are you at the rep cap? Would this post put you over?
    • If yes, the delta of effective rep, which may be as low as 0, is determined
    • Otherwise the delta is +5 or +10 depending on post type
  3. Record the Reputation change (Update User Set Reputation=@NewRep...)
  4. Record the RepHistory entry (Insert Into RepHistory...)

Every other case is more complicated. For example an upvote reversal:

  1. Were you at the cap?
    • If so, queue for a recalc since we need to re-walk votes and see if others that day counted now
  2. Deduct the TargetRepChange
  3. Record the Reputation change (Update User Set Reputation=@NewRep...)
  4. Record the RepHistory entry (Insert Into RepHistory...)

Then there's the more extreme cases where we need to walk votes for everyone involved, let's say a post is deleted (or undeleted, this includes migrations):

  1. Find anyone with a rep impact:
    • The owner
    • Downvoters (if an answer, question downvotes were free)
    • Anyone with an approved suggested edit, and a few others.
  2. If they were impacted, queue them for a recalc, again this requires a re-crawl of votes

There are another dozen or so cases off the top of my head, but there's no reason to go into detail on every single one, you get the idea. If there's some crazy corner case (there are many) that you're curious about in particular, comment.

  • 3
    Fantastic answer! Mar 26, 2012 at 21:11
  • Wow! Now I understand your first comment. I guess I need to read this answer again and again. Thanks for your time.
    – Chibuzo
    Mar 26, 2012 at 21:17
  • 3
    @Chibuzo - there are other pieces of course, like how the background recalc queue works, etc...if you have specific questions feel free to ask, none of this is secret stuff really, just not laid out anywhere until now. Mar 27, 2012 at 0:35
  • 6
    Before this gets closed out to 'thank you\'s' ......... THANK YOU for the excellent answer :)
    – d-_-b
    Sep 10, 2012 at 23:20

You must log in to answer this question.

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