SQLite Query: How to find out the Average of last X records for every person

Question

Let's say I have two SQLite tables:

Table Players
id|name

Table Scores
id|player_id|score|timestamp

What would an SQLite query looks like, if I want to calculate the average scores for every players, but only from 5 of their latest scores. I've a working query that only works to calculate the average from all scores (using GROUP BY score and AVG(score)).

Basically I need to do a SELECT statement that first returns last 5 scores for all players, and then GROUP the results by player and average the scores, but not sure how to begin. Thanks.

Answer 1

SELECT p.id, p.name, avg(s.score) 
FROM Players p 
JOIN Scores s ON p.ID = s.Player_id 
WHERE 4 < (SELECT count(*) from Scores s1 WHERE s.player_id = s1.player_id AND s.timestamp < s1.timestamp) 
p.GROUP BY p.id

This assumes that the score id field is the id of the score

Answer 2

How about this...

SELECT id,
       name,
       (SELECT AVG(score)
            FROM Scores
            WHERE Scores.player_id = Players.id AND
                  Scores.id IN (SELECT id
                                    FROM Scores Sc
                                    WHERE Sc.player_id = Players.id
                                    ORDER BY timestamp DESC
                                    LIMIT 5))
    FROM Players;