Retrieving first instance of change event using conditional_change_event
Conditional Change Event (CONDITIONAL_CHANGE_EVENT) is a versatile Snowflake function that returns a window event number for each row when the value of the expression is different from the value in the previous row.
This can be a very powerful function to use when analyzing events data or time-series data. For e.g. if you need to retrieve the first update_date by Support Engineer per Case Status change in the following data:
 |
We need the first status_date by Support Engineer per Case Status change |
with support_cases as (
select *
, conditional_change_event (status || support_engineer)
over (partition by case_number order by update_date asc) as change_rank_number
from support_case
);
 |
change_rank_number increases each time the Support Engineer + Status combination changes |
with support_cases as (
select *
, conditional_change_event (status || support_engineer)
over (partition by case_number order by update_date asc) as change_rank_number
from support_case
)
select * from support_cases
qualify row_number() over (partition by case_number, change_rank_number order by update_date) = 1;
 |
first status_date by Support Engineer per Case Status change |
See also