Sqlite query to retrieve all the records that have changed due to a certain column

Question

I would like to retrieve all records from my table where a status change has happened in one of the columns in my table. Please look into the following table :

   BANFN    BSART   FRGKZ           ERNAM   ERDAT
   11111111 NB      2                A      1533247200
   11111111 NB      4                A      1534111200
   22222222 NB      2                C      1548111600
   22222222 NB      4                C      1559858400
   33333333 NB      2                D      1559858400
   33333333 NB      4                D      1559858400
   44444444 NB      2                E      1559858400

My question now is

1. I want to retrieve all the BANFN basis wherever there was a change in the column FRGKZ from 2 to 4

2. Then another column where I want to see the lag between these two events from the ERDAT field which is in a Unix timestamp format

Sor For example for the first BANFN 11111111 the output should look like the following :

    BANFN    ERNAM BSART   LAG_In_Between                           Final_Status_FRGKZ
    11111111   A      NB   ERDAT at status 4 - ERDAT at status 2     4

The output should have the following columns :

BANFN ERNAM BSART LAG_In_Between Final_Status_FRGKZ

Answer 1

This is a convenient place to use lag():

select t.*, (erdate - prev_erdat)
from (select t.*,
             lag(frgkz) over (partition by banfn order by erdat) as prev_frgkz,
             lag(frgkz) over (partition by banfn order by erdat) as prev_erdat
      from t
      where frgkz in (2, 4)
     ) t
where frgkz = 4 and prev_frgkz = 2;

Here is a db<>fiddle.

Note that the filtering is quite important. The subquery filters to just "2"s and "4"s, so the previous is always a "2" or a "4".

The outer query only returns the "4"s where the previous is a "2".