How do I calculate participants session time in this case?

Question

I have a table of user travel sessions:

CREATE TABLE participants
(
    id INT, 
    session_id INT, 
    distance DOUBLE PRECISION, 
    duration DOUBLE PRECISION, 
    start_time INT, 
    end_time INT
);

INSERT INTO participants (id, session_id, distance, duration, start_time, end_time)
VALUES (10, 1, 1452.16, 941.989, 115866, 116808),    
       (10, 3, 2812.62, 1843.658, 116810, 118653),
       (10, 9, 91.6784, 33.677, 118689, 118722),
       (12, 1, 180.556, 79.839, 118802, 118881),
       (12, 3, 355.361, 91.186, 118910, 119001),
       (12, 5, 82.0248, 35.989, 119013, 119049),
       (12, 7, 5.33002, 6.997, 119055, 119062),
       (13, 1, 10583.8, 494.003, 279157, 279651),
       (13, 3, 2.22556, 5.02, 279654, 279659),
       (13, 5, 67.821, 60.039, 279665, 279725)

SELECT * 
FROM participants 
LIMIT 5;


id  session_id  distance    duration    start_time  end_time
10     1        1452.16     941.989      115866     116808
10     3        2812.62    1843.658      116810     118653
10     9        91.6784      33.677      118689     118722
12     1        180.556      79.839      118802     118881
12     3        355.361      91.186      118910     119001

I want a new column stay_time, to store the duration a participant stays after current session, before the start of new session. Obviously, is this the different between current session's start time and the end time of the previous one.

I would like to create a new table with all columns in participants table and the new column (say: table participants_B)

Required table (participants_B) :

id  session_id  distance    duration    start_time  end_time stay_time
10      1       1452.16      941.989     115866      116808      0
10      3       2812.62     1843.658     116810      118653      2
10      9       91.6784       33.677     118689      118722     36
12      1       180.556       79.839     118802      118881      0
12      3       355.361       91.186     118910      119001     29
12      5       82.0248       35.989     119013      119049     12
12      7       5.33002        6.997     119055      119062      6
13      1       10583.8      494.003     279157      279651      0
13      3       2.22556         5.02     279654      279659      3
13      5        67.821       60.039     279665      279725      6

db_fiddle

Answer 1

You can use lag():

select p.*,
    start_time - lag(end_time, 1, start_time) over(partition by id order by session_id) stay_time
from participants p

The 3-arguments form comes handy here; when there is no "previous" row available, we have the window function return start_time, which turns the difference to 0.

id	session_id	distance	duration	start_time	end_time	stay_time
10	1	1452.16	941.989	115866	116808	0
10	3	2812.62	1843.658	116810	118653	2
10	9	91.6784	33.677	118689	118722	36
12	1	180.556	79.839	118802	118881	0
12	3	355.361	91.186	118910	119001	29
12	5	82.0248	35.989	119013	119049	12
12	7	5.33002	6.997	119055	119062	6
13	1	10583.8	494.003	279157	279651	0
13	3	2.22556	5.02	279654	279659	3
13	5	67.821	60.039	279665	279725	6

fiddle