sqlsnowflake-cloud-data-platformsnowsql

Snowflake SQL Range between interval

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
)
Solution

  • 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