title: How to calculate percentile rank for point totals over different time spans?
body: <p>On a PHP &amp; CodeIgniter-based web site, users can earn reputation for various actions, not unlike Stack Overflow. Every time reputation is awarded, a new entry is created in a MySQL table with the <code>user_id</code>, action being rewarded, and value of that bunch of points (e.g. 10 reputation). At the same time, a field in a <code>users</code> table, <code>reputation_total</code>, is updated.</p>
  
  <p>Since all this is sort of meaningless without a frame of reference, I want to show users their percentile rank among all users. For total reputation, that seems easy enough. Let's say my <code>user_id</code> is <code>1138</code>. Just count the number of users in the <code>users</code> table with a <code>reputation_total</code> less than mine, count the total number of users, and divide to find the percentage of users with a lower reputation than mine. That'll be user 1138's percentile rank, right? Easy!</p>
  
  <p>But I'm also displaying reputation totals over different time spans--e.g., earned in the past seven days, which involves querying the reputation table and summing all my points earned since a given date. I'd also like to show percentile rank for the different time spans--e.g., I may be 11th percentile overall, but 50th percentile this month and 97th percentile today. </p>
  
  <p>It seems I would have to go through and find the reputation totals of all users for the given time span, and then see where I fall within that group, no? Is that not awfully cumbersome? What's the best way to do this?</p>
  
  <p>Many thanks.</p>