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:
I want to write an SQL query that returns a table
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
? 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).