Posgresql LAG with condition

Question

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?

Answer 1

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;

Answer 2

As per the comment: window functions let you partition your input, narrowing down the context of each window the way you want it:

select *, 
       coalesce(data-(lag(data) over w1),0) as data_diff
from   test
window w1 as (partition by user order by date asc) 
order by date, 
        "user";

It's also handy to define the window separately to save space and handle null for first row in case of lag() or last row for lead() with coalesce().

Online demo