Oracle Update n-1 Column With n-2 Data if available else 'NEW'

Question

we are using Oracle v12+

we have a situation where we need to update status column to previous value whenever we have first RENEW for every ID (it is functional ID and has many rows against one ID).

See sample i/o below. Data is sorted by timestamp for each ID.

Do we need specific joins to update? I have copied data to temp table for that but not getting success.

Table name: table_book_status

Input
[id]        [word]  [status]   [timestamp]
B000JMLBHU  book    RENEW
B000JMLBHU  read    RENEW
B000JMLBHU  was     MODIFY
B000JMLBHU  story   ADD
B000R93D4Y  with    RENEW
B000R93D4Y  book    RENEW
B000R93D4Y  story   RENEW
B000R93D4Y  was     MODIFY
B000R93D4Y  have    ADD
B001892DGG  was     ADD
B001892DWA  was     ADD
B001BXNQ2O  was     RENEW
B001BXNQ2O  book    RENEW
B001H55R8M  was     MODIFY
B001HQHCBQ  was     ADD
B001HQHCBQ  story   ADD
B001HQHCBQ  bella   ADD
B001HQHCBQ  with    ADD
B001HQHCBQ  love    ADD
B001HQHCBQ  zsadist ADD


Output
[id]        [word]  [status]    [timestamp]
B000JMLBHU  book    RENEW
B000JMLBHU  read    **MODIFY**
B000JMLBHU  was     MODIFY
B000JMLBHU  story   ADD
B000R93D4Y  with    RENEW
B000R93D4Y  book    RENEW
B000R93D4Y  story   **MODIFY**
B000R93D4Y  was     MODIFY
B000R93D4Y  have    ADD
B001892DGG  was     ADD
B001892DWA  was     ADD
B001BXNQ2O  was     RENEW
B001BXNQ2O  book    **ADD**
B001H55R8M  was     MODIFY
B001HQHCBQ  was     ADD
B001HQHCBQ  story   ADD
B001HQHCBQ  bella   ADD
B001HQHCBQ  with    ADD
B001HQHCBQ  love    ADD
B001HQHCBQ  zsadist ADD

Answer 1

You can use lag and sum window function as follows:

select id, word, 
       case when sm = 1 and status = 'Renew' 
            then coalesce(lgst, 'Add') 
       else status
       end as status, 
       timestamp 
from
    (select id, word, status, 
            sum(case when status = 'Renew' then 1 else 0 end) over (partition by id order by timestamp) as sm,
            lag(status) over (partition by id order by timestamp) as lgst, timestamp
       from your_table)