Snowflake SQL Range between interval

Question

I have input table with following structure - ID,Date, Value.

I am trying to calculate minimum value in last 10 months for every record in dataset. For that I am using range between interval.

The code below is working fine in SPARK SQL but for some reason I can't use the same code in snowflake SQL. Appreciate if someone can guide me on how to modify the below code to run in Snowflake SQL.

select *,
min(avg_Value) OVER (
        PARTITION BY ID 
        ORDER BY CAST(Date AS timestamp)  
        RANGE BETWEEN INTERVAL 10 MONTHS PRECEDING AND CURRENT ROW) as min_value_in_last_10_months
from        
(
select  ID,
        Date,
        avg(Value) as avg_Value
from table
group by ID,Date
)

The documentation is pretty clear: "For sliding window frames: RANGE is not supported." (https://docs.snowflake.com/en/sql-reference/functions-analytic.html) — Gordon Linoff, Oct 13 '20 at 14:44
I can't think of alternate way of doing this, but there should be one — , Oct 13 '20 at 15:39

score 3 · Accepted Answer · answered Oct 13 '20 at 17:38

Snowflake supports lateral joins, so one method is:

select . . .
from t cross join lateral
     (select avg(t2.value) as avg_value
      from t t2
      where t2.id = t.id and
            t2.date >= t.date - interval 10 month and
            t2.date <= t.date
     ) a

Snowflake SQL Range between interval

1 Answers1

Linked