CODEWITHSUNDEEP
sqldatabasesqliteaveragewindow-functions
Ido
Ido

Reputation: 420

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

Upvotes: 1

Views: 203

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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

Upvotes: 2

forpas
forpas

Reputation: 164139

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.

Upvotes: 3

Related Questions