Alternatives to sliding window frames with RANGE on snowflake

Question

I'm migrate some BigQuery code to snowflake, and I'm having a little trouble to using Window function with range. This is because on Snowflake range sliding window frames is not supported. I need to use "RANGE BETWEEN 2 PRECEDING AND CURRENT ROW" logic any thoughts on how we can replicate this logic on snowflake?

WITH base AS (
  SELECT * FROM (
    SELECT 7 AS num
    UNION ALL SELECT 2 AS num
    UNION ALL SELECT 2 AS num
    UNION ALL SELECT 5 AS num
    UNION ALL SELECT 3 AS num
    UNION ALL SELECT 7 AS num
    UNION ALL SELECT 7 AS num
))
SELECT
num,
COUNT(*) OVER (ORDER BY num ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS row_count,
COUNT(*) OVER (ORDER BY num RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS range_count,
SUM(num) OVER (ORDER BY num ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS row_sum,
SUM(num) OVER (ORDER BY num RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS range_sum
FROM base
ORDER BY num

Expecting result:enter image description here

Answer 1

2024 update

The query from the question now runs without issue, RANGE is supported:

https://docs.snowflake.com/en/sql-reference/functions-analytic#label-range-and-row-window-frames

---

Taking the specific question from the comments: You can solve this with a sub-query.

For example, here I will find the MIN(previous value) for each row, looking back 365 days, and partitioning by customer:

with DATA as (
  select *
  from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
)

select O_CUSTKEY, O_ORDERDATE, O_TOTALPRICE
  , (
    select min(O_TOTALPRICE) 
    from DATA 
    where A.O_CUSTKEY=O_CUSTKEY
    and O_ORDERDATE between A.O_ORDERDATE-365 and A.O_ORDERDATE
  ) min_value
from DATA A
order by O_CUSTKEY, O_ORDERDATE
limit 10;