# Help: `sliding_window` (concept)

Time-based sliding windows use `RANGE BETWEEN INTERVAL … PRECEDING AND CURRENT ROW` on a timestamp `ORDER BY` column (top level or inside TRANSFORM).

## Rolling N-day sum (the canonical form)

```featureql
ROLLING_90D := arr.TRANSFORM(
    RETURN id, ts,
        SUM(amount) OVER (
            ORDER BY ts::TIMESTAMP
            RANGE BETWEEN INTERVAL '90 DAY' PRECEDING AND CURRENT ROW
        ) AS rolling_90d
    ORDER BY ts
),
```

Runnable snippet: [`help_sliding_window_interval`](/llms/help_sliding_window_interval.md)

## Common window sizes

```featureql
-- INTERVAL literals — preferred on DATE/TIMESTAMP ORDER BY
RANGE BETWEEN INTERVAL '7 DAY' PRECEDING AND CURRENT ROW
RANGE BETWEEN INTERVAL '30 DAY' PRECEDING AND CURRENT ROW
RANGE BETWEEN INTERVAL '90 DAY' PRECEDING AND CURRENT ROW
RANGE BETWEEN INTERVAL '1 YEAR' PRECEDING AND CURRENT ROW

-- Sub-second windows (e.g. rage clicks within 3 seconds)
RANGE BETWEEN INTERVAL '3 SECOND' PRECEDING AND CURRENT ROW

-- ⚠ Do NOT use DATE_SUBTRACT inside the ORDER BY for RANGE BETWEEN — results are unreliable.
-- Use ORDER BY ts::TIMESTAMP with INTERVAL frames, or TO_UNIXTIME + integer seconds (below).
```

## Numeric RANGE on epoch seconds (alternative)

When you need an explicit second offset without INTERVAL syntax, convert to epoch seconds:

```featureql
ROLLING_90D := arr.TRANSFORM(
    RETURN id, ts,
        SUM(amount) OVER (
            ORDER BY TO_UNIXTIME(ts::TIMESTAMP)
            RANGE BETWEEN 7776000 PRECEDING AND CURRENT ROW  -- 90 * 86400
        ) AS rolling_90d
    ORDER BY ts
),
```

Runnable snippet: [`help_sliding_window_rolling_sum`](/llms/help_sliding_window_rolling_sum.md)

## Partitioned window (per entity within a shared array)

```featureql
ROLLING_BY_CAT := arr.TRANSFORM(
    RETURN id, category, ts,
        SUM(amount) OVER (
            PARTITION BY category
            ORDER BY ts::TIMESTAMP
            RANGE BETWEEN INTERVAL '30 DAY' PRECEDING AND CURRENT ROW
        ) AS rolling_30d
    ORDER BY ts
),
```

Runnable snippet: [`help_sliding_window_partitioned`](/llms/help_sliding_window_partitioned.md)

# Help: `sliding_window` (snippet)

## `help_sliding_window_interval`

```slt
# tags: sliding_window, rolling_window, range_between, range_interval
# schema: ARRAY<ROW<id BIGINT, ts DATE, rolling_90d BIGINT>>
query OUT:Q
DEFINE
    events := ZIP(
        ARRAY(1::BIGINT, 2::BIGINT, 3::BIGINT, 4::BIGINT, 5::BIGINT) AS id,
        ARRAY(
            DATE '2024-01-01',
            DATE '2024-02-15',
            DATE '2024-03-20',
            DATE '2024-04-10',
            DATE '2024-07-01'
        ) AS ts,
        ARRAY(100::BIGINT, 200::BIGINT, 150::BIGINT, 300::BIGINT, 50::BIGINT) AS amount
    ),
    rolling := events.TRANSFORM(
        RETURN
            id,
            ts,
            rolling_90d := SUM(amount) OVER (
                ORDER BY ts::TIMESTAMP
                RANGE BETWEEN INTERVAL '90 DAY' PRECEDING AND CURRENT ROW
            )
        ORDER BY ts
    )
RETURN
    out := rolling
;
----
[{id: 1, ts: 2024-01-01, rolling_90d: 100}, {id: 2, ts: 2024-02-15, rolling_90d: 300}, {id: 3, ts: 2024-03-20, rolling_90d: 450}, {id: 4, ts: 2024-04-10, rolling_90d: 650}, {id: 5, ts: 2024-07-01, rolling_90d: 350}]
```

## `help_sliding_window_rolling_sum`

```slt
# tags: sliding_window, rolling_window, range_between
# schema: ARRAY<ROW<id BIGINT, ts DATE, rolling_90d BIGINT>>
query OUT:Q
DEFINE
    events := ZIP(
        ARRAY(1::BIGINT, 2::BIGINT, 3::BIGINT, 4::BIGINT, 5::BIGINT) AS id,
        ARRAY(
            DATE '2024-01-01',
            DATE '2024-02-15',
            DATE '2024-03-20',
            DATE '2024-04-10',
            DATE '2024-07-01'
        ) AS ts,
        ARRAY(100::BIGINT, 200::BIGINT, 150::BIGINT, 300::BIGINT, 50::BIGINT) AS amount
    ),
    rolling := events.TRANSFORM(
        RETURN
            id,
            ts,
            rolling_90d := SUM(amount) OVER (
                ORDER BY TO_UNIXTIME(ts::TIMESTAMP)
                RANGE BETWEEN 7776000 PRECEDING AND CURRENT ROW
            )
        ORDER BY ts
    )
RETURN
    out := rolling
;
----
[{id: 1, ts: 2024-01-01, rolling_90d: 100}, {id: 2, ts: 2024-02-15, rolling_90d: 300}, {id: 3, ts: 2024-03-20, rolling_90d: 450}, {id: 4, ts: 2024-04-10, rolling_90d: 650}, {id: 5, ts: 2024-07-01, rolling_90d: 350}]
```

## `help_sliding_window_partitioned`

```slt
# tags: sliding_window, rolling_window, range_between
# schema: ARRAY<ROW<id BIGINT, category VARCHAR, ts DATE, rolling_30d BIGINT>>
query OUT:Q
DEFINE
    events := ZIP(
        ARRAY(1::BIGINT, 2::BIGINT, 3::BIGINT, 4::BIGINT, 5::BIGINT, 6::BIGINT) AS id,
        ARRAY('A', 'B', 'A', 'B', 'A', 'B') AS category,
        ARRAY(
            DATE '2024-01-01',
            DATE '2024-01-05',
            DATE '2024-01-20',
            DATE '2024-01-25',
            DATE '2024-02-10',
            DATE '2024-02-20'
        ) AS ts,
        ARRAY(
            100::BIGINT,
            200::BIGINT,
            150::BIGINT,
            300::BIGINT,
            50::BIGINT,
            75::BIGINT
        ) AS amount
    ),
    rolling := events.TRANSFORM(
        RETURN
            id,
            category,
            ts,
            rolling_30d := SUM(amount) OVER (
                PARTITION BY category
                ORDER BY ts::TIMESTAMP
                RANGE BETWEEN INTERVAL '30 DAY' PRECEDING AND CURRENT ROW
            )
        ORDER BY
            ts,
            id
    )
RETURN
    out := rolling
;
----
[{id: 1, category: A, ts: 2024-01-01, rolling_30d: 100}, {id: 2, category: B, ts: 2024-01-05, rolling_30d: 200}, {id: 3, category: A, ts: 2024-01-20, rolling_30d: 250}, {id: 4, category: B, ts: 2024-01-25, rolling_30d: 500}, {id: 5, category: A, ts: 2024-02-10, rolling_30d: 200}, {id: 6, category: B, ts: 2024-02-20, rolling_30d: 375}]
```

**Related tags**: [`to_unixtime`](/llms/to_unixtime.md) [`episode_merging`](/llms/episode_merging.md) [`locf`](/llms/locf.md) [`tiebreaking`](/llms/tiebreaking.md) [`cumulative_sum`](/llms/window_functions.md)