# Rolling Window

Use the ROWS BETWEEN keyword to specify a rolling window over which running total functions (for example RUNAVG) are computed.

Running total functions such as RUNSUM, RUNAVG, RUNMIN and RUNMAX can be specified with rolling window or a shifted boundary. An example could be a moving average used with time series data to smooth out short-term fluctuations and highlight longer-term trends or cycles.

ROWS BETWEEN does not currently support the COUNT or APPROXIMATE_COUNT function.

## Syntax

``````SELECT (RUNNING TOTAL)(...) ROWS BETWEEN (...) AND (...)
SELECT RUNAVG (...) ROWS BETWEEN (…) AND (…)
``````

## Examples

In the context of Date

Define a moving average

``````SELECT RUNAVG (Revenue) ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
``````

Return Year-to-Date (YTD) for Revenue:

``````SELECT RUNSUM (Revenue) WITHIN Year (Opp. Close) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
``````

Define five days preceding and the current date:

`````` SELECT RUNAVG({metric}) ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
``````
``````SELECT RUNAVG({metric}) ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
SELECT RUNAVG({metric}) ROWS BETWEEN 5 PRECEDING AND 1 FOLLOWING
``````

Display year to date - the same result as without specifying ROWS BETWEEN statement:

``````SELECT RUNSUM({metric/m}) WITHIN {attribute/year} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
``````

Define year to yesterday:

``````SELECT RUNSUM({metric}/m) WITHIN {attribute/year} ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ROW
``````

In the context of Month

Define the last twelve months:

``````SELECT RUNSUM({metric/m}) ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING
``````

Define everything before the current month:

`````` SELECT RUNAVG({metric/m}) WITHIN {attribute/year} ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
``````