Sql Return The Record Where The Value In A Column Have Changed

I have data in Hive table which look something like this - I would like to get a summarized data where the value in event column has changed from previous value. The data points a

Solution 1:

You can use lag():

select t.*
from (select t.*, 
             lag(event) over (partition by vin order by start) as prev_event
      from t
     ) t
where prev_event isnullor prev_event <> event;

This looks at the changes by time and vin. I'm not sure if the mode is relevant too. If so, add it to the partition by.