Databases
StackOverflow2013
Postsdbo
POHow to calculate percentile rank for point totals over different time spans?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to calculate percentile rank for point totals over different time spans?
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USToph
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to calculate percentile rank for point totals over different time spans?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to calculate percentile rank for point totals over different time spans?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload