I have a table with customer segment data. It has customer id, device, segment name and timestamp. We want to capture the changes in customer segments. For example customer 1 can be part of segment S1,S2 at 1AM, at 2AM same customer is part of s1 and s3 (At 2AM customer removed from segment s2 and s3 is added). MY output table should have 3 records. s1 will have start time as 1AM and end time as ‘9999-01-01’ and s2 should have start time as 1AM and end time as 2AM and s3 should have start time as 2AM and end time as 9999-01-01. If same customer rejoins the segment s2 at 4AM then there should be one more record for s2 with start time as 4AM and end time as ‘9999-01-01’, there is no change to s1 and s3 as customer is still part of those segments at 4AM.

sample data:
enter image description here

expected output:
enter image description here