# 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

userIDfield1field2field3field4
1
2
3

### purchases

purchaseIDuserIDpricetimestamp
12705166323
21306543654
311005456434
42305846541
52409635322
61502541541

I want to write an SQL query that returns a table

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