How to get the biggest differences between two fields comparing all users?

Question

I have a table that has the columns skill1xp, skill1lvl, and skill1rank for 25 skills. Each day I update it for all users, so there's a datetime column. I'm making a high-scores progress tracker for a game, for many users, and I've got just one feature left, which is Highest Gains for a certain period of time (day/week/month).

I guess this is a hefty question, but...how would I go about doing that? =/.

Table structure: http://pastebin.com/m5903aa17

Answer 1

if you want to know the Highest Gains for a certain period of time per user, you certainly needs to process data out of SQL stored procedures.
Get all data in a DataTable then find the max value for a period.

let's say you want to find the Max Gain for a period of 1 week.

days: 01|02|03|04|05|06|07|08|09|10|11|12|13|14|15
      |....................|
        |....................|
           |....................| 
                    .... etc ...
                            |....................|

int minDay = 1, daysPeriod = 7, maxDay = 15, maxGain = 0, startDay = 0;
for(int i = minDay; i < maxDay - daysPeriod; i++)
{
      int value = 0;
      for(int k = 0; k < daysPeriod; i++)
        value += (int)dataTable.Rows[i + k]["Gain"];
      if(value >= maxGain)
      {
        maxGain = value;
        startDay = i;
      }
}

Next, simply present the start date and period to the user, and the Max Gain of course.
This algorithm can be optimized if you use an array of 7 (daysPeriod) and iterate just once until reach all 15 elements.

Answer 2

Well, in order to display "who won the most XP in a given ", you need to store how many XP users win at a granularity of at LEAST "X". The easiest solution is almost certainly to store the XP won and what the timestamp was, giving you an "X" of seconds.

create table xprewarded (
  track_users_id int(11) NOT NULL,
  xpreward int(11) NOT NULL,
  rewardtime timestamp NOT NULL DEFAULT NOW()
);

To get the person who won the most XP in the past day:

select 
    sum(xpreward), 
    track_users_id 
from xprewarded 
where rewardtime > now()-86400
group by track_users_id 
order by sum(xpreward) desc limit 1;

"now()-86400" being the filter that specifies "from now, look back 86400 seconds (or '24 hours'), and return any record that was created since that point." Other time periods can be accomplished with similar math.

Answer 3

@TML: I need to get the differences of XP, Rank, and Level for everyone though, not just XP.

@pixel3cs:

Your post got me thinking. If I loop through all the users, I could put the differences into an array if they are bigger than the already existing value. So, I think this would work: http://pastebin.com/m26b60996

Answer 4

I assume you write a new record to the track_users table whenever one of the stats change.

To get the change of a stat between two given dates ($START_DATE and $END_DATE), for all user you can use the following query:

SELECT a.id, a.date, b.date, b.skill1rank - b.skill1rank AS skill1rank_gain
    FROM track_users AS a 
    INNER JOIN track_users AS b
          ON a.date = $START_DATE AND 
             b.date = $END_DATE AND 
             a.id = b.id
    ORDER BY skill1rank_gain DESC