retrieving-first-instance-of-change-event-using-conditional_change_event.md

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

{% for page in site.pages %} {% if (page.title contains "conditional_true_event" or page.title contains "Conditional True Event" or page.title contains "Conditional Change Event") or page.title contains "conditional_change_event"%}
• {{ page.title | escape }}
{% endif %} {% endfor %}