Determining how far away is the next ID
The LEAD function can be used to find the next start_time per ID.
And the ROW_NUMBER function can return a unique sequencial number per ID.
SELECT *
, LEAD(start_time) OVER (PARTITION BY ID ORDER BY start_time) AS next_timestamp
, DATEDIFF(minute, start_time, LEAD(start_time) OVER (PARTITION BY ID ORDER BY start_time)) AS time_diff
, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY start_time) AS entry_order
FROM your_table
ORDER BY start_time
Using LEAD
, DATEDIFF
and ROW_NUMBER
:
SELECT *,
LEAD(start_time) OVER(PARTITITON BY ID ORDER BY start_time) AS next_timestamp,
DATEDIFF(seconds, start_time, next_timestamp) SA time_difference,
ROW_NUMBER() OVER(PARTITITON BY ID ORDER BY start_time) AS entry_order
FROM tab