Find the average over the n last purchases, for every user in the table using SQL

I have a SQLite db with two tables:

users

userID	field1	field2	field3	field4
1
2
3

purchases

purchaseID	userID	price	timestamp
1	2	70	5166323
2	1	30	6543654
3	1	100	5456434
4	2	30	5846541
5	2	40	9635322
6	1	50	2541541

I want to write an SQL query that returns a table

userID	field1	field2	field3	field4	avgPrice
1
2
3

where avgPrice corresponds the the average price of the user’s n last purchases.

I managed to write the following query, which calculates the average price of each user’s purchases:

SELECT
    users.userID,
    users.field1,
    users.field2,
    users.field3,
    users.field4,
    avg(purchases.price)
FROM purchases
JOIN users on users.userID = purchases.userID
GROUP BY purchases.userID

But I could not think of any efficient way to calculate the average over the last n purchases. (The only way I could find involved inner queries and was significantly inefficient).

I would appreciate your help solving this problem. (FYI the tables here are simplified versions of the tables I actually use).

Answer

You can use ROW_NUMBER() window function to rank the purchases starting from the last one and then join to the table users to aggregate:

SELECT u.*, COALESCE(AVG(p.price), 0) avgPrice
FROM users u
LEFT JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY userID ORDER BY timestamp DESC) rn
  FROM purchases
) p ON p.userID = u.userID AND p.rn <= ?
GROUP BY u.userID

Change ? to the number n that you want.