SQL – Get the antepenultimate (before previous group/phase)

I have the following table and I’d like to get the antepenultimate or the value before the previous value.

I already have group, value and prev_value, dateint… I’m trying to derive prev_prev_value

 grp  value  prev_value    prev_prev_value     dateint
 -----------------------------------------------------------
 1       x       null         null               20200101
 1       x       null         null               20200102
 2       y        x           null               20200103
 2       y        x           null               20200104
 2       y        x           null               20200105
 3       z        y             x                20200106
 3       z        y             x                20200107
 3       z        y             x                20200108
 4       a        z             y                20200109

This is the table with test data (as a CTE)

with test as (
    select  1  as grp,     'x' as value ,      null as prev_value , 20200101 as dateint
    union
    select  1  as grp,     'x' as value ,      null as prev_value , 20200102 as dateint
    union
    select  2  as grp,     'y' as value ,      'x' as prev_value  , 20200103 as dateint
    union
    select  2  as grp,     'y' as value ,      'x' as prev_value  , 20200104 as dateint
    union
    select  2  as grp,     'y' as value ,      'x' as prev_value ,  20200105 as dateint
    union
    select  3  as grp,     'z' as value ,      'y' as prev_value ,  20200106 as dateint
    union
    select  3  as grp,     'z' as value ,      'y' as prev_value ,  20200107 as dateint
    union
    select  3  as grp,     'z' as value ,      'y' as prev_value ,  20200108 as dateint
    union
    select  4  as grp,     'a' as value ,      'z' as prev_value ,  20200109 as dateint

)

Any ideas on how to derive prev_prev_value I’d like to use window functions and avoid joins.

I’ve tried LAG but I have not been successful.

Answer

It appears that a nested FIRST(LAG.. did the job:

The logic behind that was to get the previous (lag) prev_value and get the FIRST value of that group

select grp,
       value,
       prev_value,
       FIRST(LAG(prev_value) over (order by dateint)) over (partition by grp order by dateint) as prev_prev_value,
       dateint
from test order by dateint

Advertisement

Answer