Parameterize LAG Window Function

Snowflake Offset Parameter

Background

In this case we want to give our users the ability to decide how many rows back to include in our LAG function.

Offset Parmater

-- B (number input control)
SELECT
  3 AS "value"
-- DATA
select row_number() over(order by DATE asc)row_num, DATE,TEMP from PUBLIC.LONDON_WEATHER limit 10 

The hard-coded way:

Typically, you would just add 3 as the offset for lag to get the temp 3 days ago.
But we need to parameterize that, so we need to do something else.

Attempt 1: Failed

It’s tempting to just replace 3 with the subquery to get the value from B but Snowflake requires this to be a scalar. It won’t even work if you put in (1+1)

Attempt 2: Cross Join

To get around this, we can cross join the data back to itself, but join on a variable row_number:
In this example we join DATA to itself, but only when the row_number = row_number - (offset parameter).

So the full code would be:

SELECT B.<lag_col>, A.*
FROM A
LEFT JOIN A AS B ON A.ROW_NUMBER - {offset_parameter} = B.ROW_NUMBER

where:

  • A has a row_number column in the desired order
  • <lag_col> is the column name of the item you want to LAG
  • {offset_parameter} is the variable offset you want to use