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

Solution for Find the average over the n last purchases, for every user in the table using SQL
is Given Below:

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
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).

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.

You can use a correlated subquery for this:

SELECT u.*,
(SELECT AVG(p.price)
FROM (SELECT p.price
FROM purchases p
WHERE p.userID = u.userID
ORDER BY p.timestamp DESC
LIMIT ?    -- whatever number you wnat
) p
) as avg_price
FROM users u;

For performance, you want an index on purchases(userID, timestamp desc, price).