By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table tbl (fid int, result int, time int);
insert into tbl values
(1, 10, 100),
(1, 9, 85),
(2, 9, 92),
(2, 9, 99),
(3, 8, 88),
(3, 7, 100),
(3, 8, 67),
(4, 5, 50),
(4, 5, 50)
9 rows affected
SELECT fid, time,
time - LAG(time, 1) OVER (PARTITION BY fid ORDER BY time) as Difference_Between_Times
FROM tbl
| fid | time | difference_between_times |
|---|---|---|
| 1 | 85 | null |
| 1 | 100 | 15 |
| 2 | 92 | null |
| 2 | 99 | 7 |
| 3 | 67 | null |
| 3 | 88 | 21 |
| 3 | 100 | 12 |
| 4 | 50 | null |
| 4 | 50 | 0 |