MySQL limit number of rows used to calculate the SUM

I'm making a game where the score for the player is calculated hourly. The score is needed so the leader board can show the best ranked players. 'points' won in a game are stored for the player in the boards_played table. The players table has a score field which is updated hourly with this SQL:

update players p 
       inner join (
          SELECT player_id, sum(points) as total 
            from boards_played
           where time_played > DATE_SUB(now(), INTERVAL 7 DAY)
           GROUP BY player_id) s on p.player_id = s.player_id
  set p.score = s.total

The problem is somebody who wins a total of 50 points from 100 games should be a better rank than someone who wins 60 points from 200 games.

In short the score should only be based on the your last 100 games in the last 7 days.

Solution

You probably need a correlated subquery here, so you can retrieve the correct value for each player.

To find a certain player's summary score, this subquery does it.

          SELECT SUM(points) as total
            FROM (
                   SELECT player_id, points
                     FROM boards_played
                    WHERE time_played > DATE_SUB(NOW(), INTERVAL 7 DAY)
                     AND player_id = ***something*** 
                    ORDER BY time_played DESC
                    LIMIT 100
                 ) a

Now, you need to incorporate that in your outer query

UPDATE players p
   SET score =
       (
          SELECT SUM(points) as total
            FROM (
                   SELECT points
                     FROM boards_played
                    WHERE time_played > DATE_SUB(NOW(), INTERVAL 7 DAY)
                      AND points.player_id = p.player_id 
                    ORDER BY time_played DESC
                    LIMIT 100
                 ) a
       )

Why is this called correlated? The line points.player_id = p.player_id in the subquery correlates it to the outer query. Doing it this way allows the LIMIT to be applied separately to each player's points.

But, you might be better off making a view that can compute this value on the fly, rather than updating your table. Then you don't have to worry about updating your table all the time. It would look something like this (not debugged).

CREATE VIEW players_with_score AS
SELECT p.player_id, p.col1, p.col2, p.col3,
       (  
          SELECT SUM(points) as total
            FROM (
                   SELECT points
                     FROM boards_played
                    WHERE time_played > DATE_SUB(NOW(), INTERVAL 7 DAY)
                      AND points.player_id = p.player_id 
                    ORDER BY time_played DESC
                    LIMIT 100
                 ) a
       ) score
  FROM player p

Then you can say things like

  SELECT player_id, score
    FROM players_with_score
   WHERE score > 250