I am working on customer data where customer phase can change based on different factors. I need to find out every phase and the time it was changed to that phase.

Sample Data:

Expected Result:

I tried to use Lag function but it is repeating.

Thank you

CodePudding user response：

lag can help here, but you need to add some filtering and deal with null in lag result

with ordered_data as (
    select *
         , lag(phase) over (order by phase_date desc) prev_phase
         , lag(phase_date) over (order by phase_date desc) prev_phase_date
    from monitoring.st1
)
select *
from ordered_data
where phase != prev_phase

union

(
    select *
         , phase as prev_phase
         , phase_date as prev_phase_date
    from monitoring.st1
    order by phase_date
    limit 1
)
order by phase_date desc

it generates the following result, where prev_phase and prev_phase_date hold result which you're looking for