Extract previous row calculated value for use in current row calculations - sql

Question

I have a below table with expected output in column result

id  col1   col2   col3  result
1   10     30     2     30
2   15     10     8     28
3   25     10     5     25
4   20     25     9     25
5   30     15     4     30

result = max(col1, col2, (previous_row's) result - (previous row's) col3)

For example: For the first row, its max(10, 30)=30 and for second row its max(15,10,30-2)=28

Can this be done in sql efficiently using partition? I have figured out a way to do this part:((previous_row's) result - (previous row's) col3) but stuck because of the complexity involving max function using value from its previous row as an input.

Update: I use below sql to get ((previous_row's) result - (previous row's) col3)

FIRST_VALUE(max(col1, col2)) over (order by id)
            -IFNULL(sum(col3) over (order by id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0)) 

Answer 1

Consider below approach

WITH RECURSIVE result_cte AS (
  SELECT *, GREATEST(col1, col2) AS result
  FROM your_table
  WHERE id = 1
  UNION ALL
  SELECT t.*, GREATEST(t.col1, t.col2, r.result - r.col3) AS result
  FROM your_table t
  JOIN result_cte r
  ON t.id = r.id + 1
)
SELECT *
FROM result_cte
ORDER BY id    

if applied to sample dara in your questions, output is