SQL example of using row_number to find the changes on an column in time series data

find_data_changes.sql

select
   key_col
  ,startdate
  ,enddate
  ,change_col
  from (
  select distinct
       key_col
      ,startdate = min(date_col) over (partition by group_key, key_col)
      ,enddate = max(date_col) over (partition by group_key, key_col)
      ,firstdate = min(date_col) over (partition by key_col)
      ,latestdate = max(date_col) over (partition by key_col)
      ,group_key
      ,change_col
      from (
      select
          key_col
          ,date_col
          ,group_key = 
              row_number() over (partition by key_col order by date_col) - 
              row_number() over (partition by key_col, change_col order by date_col)
          ,change_col
          from tbl
      ) as z
      where date_col > '2018-01-01'
) as x
  where not (x.startdate = x.firstdate and x.enddate = x.latestdate) -- filter out key_cols that do have not changed
  order by key_col