Calculate with previous value calculated in sql databricks

Question

Please, I need help to build a query that calculate the value using the previous value calculated at the same column.

It would be something like that:

Select ID
,n
,CASE WHEN n=1 THEN col1 ELSE col1+col3 END AS col2
,LAG(col2) OVER (PARTITION BY ID ORDER BY n) AS col3
from table

Databricks returns:

The feature is not supported: Referencing a lateral column alias col2 in window expression "LAG(lateralAliasReference(col2)) OVER (PARTITION BY ID ORDER BY n ASC NULLS FIRST unspecifiedframe$())".

I tried: table2 AS (SELECT * ,col1 AS col2 FROM table WHERE n=1 UNION ALL SELECT curr.*, curr.col1+prev.col2 AS col2 FROM table AS curr INNER JOIN table2 AS prev ON curr.Id = prev.Id AND curr.n= prev.n+ 1 )

SELECT * FROM table2

It says that on "INNER JOIN table2 AS prev" , table2 cannot be found.

Answer 1

You can use CASE clause within a window function :

SELECT ID, n,
      LAG(CASE WHEN n=1 THEN col1 ELSE col1+col3 END) 
          OVER (PARTITION BY ID ORDER BY n) AS col3
FROM mytable;

Answer 2

Create col2 in a derived table to have it available.

select ID, n, col2
      ,LAG(col2) OVER (PARTITION BY ID ORDER BY n) AS col3
from (
  Select ID
        ,n
        ,CASE WHEN n=1 THEN col1 ELSE col1+col3 END AS col2
  from table
) dt