Determining how far away is the next ID

user_9393218 0
Viewed 45

So I have some data, subset as follows:

ID  data start_time 
001    X 2021-12-29 10:54:12.429 +0000
002    Y 2022-01-16 05:07:55.708 +0000 
003    Y 2021-12-31 12:25:12.980 +0000
002    A 2022-01-03 12:49:41.866 +0000
001    A 2021-12-30 16:32:13.736 +0000
001    A 2022-01-17 10:10:10.736 +0000

I would like to determine in minutes, the time difference between a given ID and the next occurrence in the dataframe, in order of start_time. So if an ID appears at 12:00 and 12:01, I would like the ID to show the time of the next entry as well as the diff in minutes, using SQL/Snowflake. CTE preferred.

The following fields should be added:

  • next_timestamp: The timestamp of the following entry
  • time_diff: The difference in minutes between start_time and next_timestamp.
  • entry_order: The number of how many of this ID has been.

Expected output:

ID  data start_time                       next_timestamp                 time_diff  entry_order
001    X 2021-12-29 10:54:12.429 +0000    2021-12-30 16:32:13.736 +0000  1778       1
001    A 2021-12-30 16:32:13.736 +0000    2022-01-17 10:10:10.736 +0000  25537      2
003    Y 2021-12-31 12:25:12.980 +0000    NULL                           NULL       1
002    A 2022-01-03 12:49:41.866 +0000    2022-01-16 05:07:55.708 +0000  18258      1
002    Y 2022-01-16 05:07:55.708 +0000    NULL                           NULL       2
001    A 2022-01-17 10:10:10.736 +0000    NULL                           NULL       3

Notice, the resulting output is ordered by the timestamp, ascending.

snowflake-cloud-data-platform datediff timestamp sql
2 Answers

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
user_40034190

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
user_50708790
Related