My data looks like this:
| id | user | data | date |
|---|---|---|---|
| 1 | 1 | 1 | 2023-02-05 |
| 2 | 2 | 1 | 2023-02-05 |
| 3 | 1 | 2 | 2023-02-06 |
| 4 | 1 | 3 | 2023-02-07 |
| 5 | 2 | 5 | 2023-02-07 |
I want to get a difference between data of each row and a previous row for this user like this:
| id | user | data | date | diff |
|---|---|---|---|---|
| 1 | 1 | 1 | 2023-02-05 | |
| 2 | 2 | 1 | 2023-02-05 | |
| 3 | 1 | 2 | 2023-02-06 | 1 |
| 4 | 1 | 3 | 2023-02-07 | 1 |
| 5 | 2 | 5 | 2023-02-07 | 4 |
I can do this with LAG function but without condition that users for difference must be same. How can I do it with condition in postgres?
>Solution :
We can use LAG() as follows:
SELECT id, user, data, date,
data - LAG(data) OVER (PARTITION BY user ORDER BY date) AS diff
FROM yourTable
ORDER BY date, user;