Getting Min/Max over multiple columns timestamp based

November 29, 2022

I am trying to determine how to get the min and max values before a change occurs in SQL (Snowflake). Here is the dataset:

TS                            Node      Person
2022-11-29 12:01:00 AM        1         Adam
2022-11-29 12:02:00 AM        1         Adam
2022-11-29 12:03:00 AM        1         Adam
2022-11-29 12:04:00 AM        2         Adam
2022-11-29 12:05:00 AM        2         Adam
2022-11-29 12:06:00 AM        1         Adam

What I am trying to get is the following:

Person    Node    Min TS                    Max TS                      Elapsed
Adam      1       2022-11-29 12:01:00 AM    2022-11-29 12:03:00 AM      2
Adam      2       2022-11-29 12:04:00 AM    2022-11-29 12:05:00 AM      1
Adam      1       2022-11-29 12:06:00 AM    Null                        Null

I have tried to RANK, but when I get to the second set of Node 1, it just continues the count.

>Solution :

It is easy to achieve with CONDITIONAL_CHANGE_EVENT window function:

Returns a window event number for each row within a window partition when the value of the argument expr1 in the current row is different from the value of expr1 in the previous row.

WITH cte AS (
 SELECT *, CONDITIONAL_CHANGE_EVENT(Node) OVER(PARTITION BY Person 
                                               ORDER BY TS) AS subgrp
 FROM tab
)
SELECT Person, MIN(Node) AS Node, MIN(TS) AS MIN_TS, MAX(TS) AS MAX_TS
FROM cte
GROUP BY Person, subgrp;