title: Average of latest N records per group
body: <p>My current application calculates a point average based on all records for each user:</p>
  
  <pre><code>SELECT `user_id`, AVG(`points`) AS pts 
  FROM `players` 
  WHERE `points` != 0 
  GROUP BY `user_id`
  </code></pre>
  
  <p>The business requirement has changed and I need to calculate the average based on the last 30 records for each user.</p>
  
  <p>The relevant tables have the following structure:</p>
  
  <p>table: players; columns: player_id, user_id, match_id, points</p>
  
  <p>table: users; columns: user_id</p>
  
  <p>The following query does not work, but it does demonstrate the logic that I am trying to implement.</p>
  
  <pre><code>SELECT @user_id := u.`id`, (
      -- Calculate the average for last 30 records
      SELECT AVG(plr.`points`) 
      FROM (
          -- Select the last 30 records for evaluation
          SELECT p.`points` 
          FROM `players` AS p 
          WHERE p.`user_id`=@user_id 
          ORDER BY `match_id` DESC 
          LIMIT 30
      ) AS plr
  ) AS avg_points 
  FROM `users` AS u
  </code></pre>
  
  <p>Is there a fairly efficient way to calculate the averages based on the latest 30 records for each user?</p>