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

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