# 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.

Syntax

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

Examples

In 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}) WITHIN {Year} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
``````

Define year to yesterday:

``````SELECT RUNSUM({metric}) WITHIN {Year} ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ROW
``````

In context of Month:

Define last twelve months:

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

Define everything before current month:

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