How to calculate percentile rank for point totals over different time spans?

I can think of a few options off the top of my head here:

1. As you mentioned, total up the reputation points earned during the time range and calculate the percentile ranks based on that.

2. Track updates to reputation_total on a daily basis - so you have a table with user_id, date, reputation_total.

3. Add some new columns to the user table (reputation_total, reputation_total_today, reputation_total_last30days, etc) for each time range. You could also normalize this into a separate table (reputation_totals) to prevent you from having to add a new column for each time span you want to track.

Option #1 is the easiest, but it's probably going to get slow if you have lots of rows in your reputation transaction table - it won't scale very well, especially if you need to calculate these in real time.

Option #2 is going to require more storage over time (one row per user per day) but would probably be significantly faster than querying the transaction table directly.

Option #3 is less flexible, but would likely be the fastest option.

Both options 2 & 3 would likely require a batch process to calculate the totals on a daily basis, so that's something to consider as well.

I don't think any option is necessarily the best - they all involve different tradeoffs of speed/storage space/complexity/flexibility. What you do will ultimately depend on the requirements for your application of course.

I don't see why that would be too overly complex. Generally all you would need is to add to your WHERE clause a query that limits results like:

WHERE DatePosted between @StartOfRange and @EndOfRange