Two SQL updates that use CASE statements and need to access previous rows to update the current one

I have a dashboard with python and dash/plotly that receives inputs from the user and then run a query on Google BigQuery.

One of the queries updates a column (POR_FT), uses other column (POR_CORR or POR_CLEAN depending of the case statement), a string input from the dashboard (cpi) and previous rows of the updated POR_FT (if rownumber != 1). The table is already created with all columns, so if I run the queries it would update the values from the table.

The query that I was trying to use for this case is:

    DECLARE cpi STRING DEFAULT "C";
    UPDATE `xx.yy.zz` t
    SET
    POR_FT = s.POR_FT
    FROM
    (
    SELECT
        RN,
        CASE
        WHEN RN = 1 AND cpi = "S" AND PAY_SHALE_COR = "PAY" THEN POR_CORR
        WHEN RN = 1 AND cpi = "C" AND PAY_CLEAN = "PAY" THEN POR_CLEAN
        WHEN RN = 1 THEN 0
        WHEN RN > 1 AND cpi = "S" AND PAY_SHALE_COR = "PAY" THEN (POR_CORR/5) + (LAG(POR_FT, 1, 0)  OVER(ORDER BY RN))
        WHEN RN > 1 AND cpi = "C" AND PAY_CLEAN = "PAY" THEN (POR_CLEAN/5) + (LAG(POR_FT, 1, 0)  OVER(ORDER BY RN))
        ELSE (LAG(POR_FT, 1, 0) OVER(ORDER BY RN))
        END
        AS POR_FT
    FROM
        `xx.yy.zz`
    WHERE
        DEPTH_M IS NOT NULL
    )
    s
    WHERE
    t.RN = s.RN

But does not work correctly, it doesn't update the column. On another question that I made, I got the response that LAG is not intended to be used this way and it would probably need a recursive approach.

The basic calculation when the user inputs "C" on cpi is: if rownumber is 1 and PAY_CLEAN = "PAY", then it simply gets the value of the column POR_CORR. If rn > 1 and PAY_CLEAN = "PAY", then it will get the value from POR_CLEAN divided by 5 plus the value of POR_FT from the previous row. Else, it will simply return the value of POR_FT from the previous row.

Here is a SAMPLE of my dataset.

The expected output should look like:

The other query looks very similar:

    DECLARE cpi STRING DEFAULT "C";
    UPDATE `xx.yy.zz` t
    SET
    t.HPV_CUM = s.HPV_CUM
    FROM
    (
    SELECT
        DEPTH_M,
        RN,
        CASE
        WHEN RN < 5 AND cpi = "S" AND PAY_SHALE_COR = "PAY" THEN HPV_LAMINATED
        WHEN RN < 5 AND cpi = "C" AND PAY_CLEAN = "PAY" THEN HPV_CLEAN
        WHEN RN < 5 THEN 0
        WHEN RN > 4 AND cpi = "S" AND PAY_SHALE_COR = "PAY" THEN (HPV_LAMINATED/5) + (LAG(HPV_CUM, 1)  OVER(ORDER BY RN))
        WHEN RN > 4 AND cpi = "C" AND PAY_CLEAN = "PAY" THEN (HPV_CLEAN/5) + (LAG(HPV_CUM, 2)  OVER(ORDER BY RN))
        ELSE (LAG(HPV_CUM, 1) OVER(ORDER BY RN))
        END
        AS HPV_CUM
    FROM
        `xx.yy.zz`
    WHERE
        DEPTH_M IS NOT NULL
    )
    s
    WHERE
    t.RN = s.RN

So, if I can solve one, I can probably use the same thing on the second one.

How can I make this work?

Thanks in advance!