Need to include the offset value as expr in LAG functions

Question

I am migrating Redshift SQL to snowflake SQL. Need suggestion on how to include the offset value as expression in snowflake's LAG(). regarding offset, Redshift supports expression in LAG() where as snowflake does not.

Eg: expected sql in SF:

LAG(exp, **exp**) over (partition by col1 order by col2)

Answer 1

Expression for second input parameter of the LAG function is currently not supported. You will receive an error as given below, if you use the pass an expression.

Error: SQL compilation error: argument 2 to function LAG needs to be constant, found 'EXPR' -- Where EXPR is an expression

An improvement request for supporting expressions in the second argument of LAG() function is in the pipeline.

Workaround You can rewrite the LAG function by adding the ROW_NUMBER() to the table and doing a Self-Join.