How to get values from the previous row?

Question

I have a table like this:

ID	NUMBER	TIMESTAMP
1	1	05/28/2020 09:00:00
2	2	05/29/2020 10:00:00
3	1	05/31/2020 21:00:00
4	1	06/01/2020 21:00:00

And I want to show data like this:

ID	NUMBER	TIMESTAMP	RANGE
1	1	05/28/2020 09:00:00	0 Days
2	2	05/29/2020 10:00:00	0 Days
3	1	05/31/2020 21:00:00	3,5 Days
4	1	06/01/2020 21:00:00	1 Days

So it takes 3,5 Days to process the number 1 process.

I tried:

select a.id, a.number, a.timestamp, ((a.timestamp-b.timestamp)/24) as days 
from my_table a
left join (select number,timestamp from my_table) b 
on a.number=b.number

Didn't work as expected. How to do this properly?

Answer 1

Use the window function lag().

With standard interval output:

SELECT *, timestamp - lag(timestamp) OVER(PARTITION BY number ORDER BY id)
FROM   tbl
ORDER  BY id;

If you need decimal number like in your example:

SELECT *, round((extract(epoch FROM timestamp - lag(timestamp) OVER(PARTITION BY number ORDER BY id)) / 86400)::numeric, 2) || ' days'
FROM   tbl
ORDER  BY id;

If you also need to display '0 days' instead of NULL like in your example:

SELECT *,  COALESCE(round((extract(epoch FROM timestamp - lag(timestamp) OVER(PARTITION BY number ORDER BY id)) / 86400)::numeric, 2), 0) || ' days'
FROM   tbl
ORDER  BY id;

db<>fiddle here