> For the complete documentation index, see [llms.txt](https://docs.koinju.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.koinju.io/sql-cookbook/mean-reversion-zscore.md).

# Mean reversion z-score

A 20-day rolling **z-score** signal generator — the textbook mean-reversion idiom. When the price drifts more than `1.25 σ` away from its rolling mean, mark it as oversold (`signal = +1`) or overbought (`signal = -1`). Otherwise flat (`0`).

```
ma_20[t]   = mean of close[t-19 … t]
std_20[t]  = sample std of close[t-19 … t]   (ddof = 1)
zscore[t]  = (close[t] − ma_20[t]) / std_20[t]
signal[t]  = +1   if zscore < −1.25
             −1   if zscore > +1.25
              0   otherwise
```

## Query

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    20 AS window_size,
    1.25 AS n_std,
    candles AS (
        SELECT toDate(start) AS day, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market = 'BTC-USDT'
          AND start BETWEEN start_date AND end_date
    ),
    z AS (
        SELECT day, close,
               avg(close) OVER w AS ma_20,
               stddevSamp(close) OVER w AS std_20,
               (close - avg(close) OVER w) / nullIf(stddevSamp(close) OVER w, 0) AS zscore,
               count(*) OVER w AS valid_rows
        FROM candles
        WINDOW w AS (ORDER BY day ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
    )
SELECT day, close, ma_20, std_20, zscore,
       if(zscore < -n_std, 1, if(zscore > n_std, -1, 0)) AS signal
FROM z
WHERE valid_rows = window_size
ORDER BY day
```

Functions used: [`toDate`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#todate), [`toFloat64`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tofloat64), [window functions (`avg`/`stddevSamp`/`count` OVER)](https://clickhouse.com/docs/sql-reference/window-functions), [`stddevSamp`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevsamp), [`nullIf`](https://clickhouse.com/docs/sql-reference/functions/conditional-functions#nullif), [`if`](https://clickhouse.com/docs/sql-reference/functions/conditional-functions#if)

Two SQL details worth flagging:

* **`stddevSamp`, not `stddevPop`.** Pandas `.rolling(N).std()` defaults to `ddof = 1` (Bessel's correction). Mixing pop / sample here changes the z-score by a factor of `√(N / (N − 1)) ≈ 1.026` for `N = 20` — small numerically but enough to flip a signal that's hovering near `±1.25`. Use `stddevSamp` to match pandas exactly.
* **`count(*) OVER w = window_size`** filters out the first 19 rows before the rolling window has filled. Same effect as `dropna(subset=['zscore'])` in pandas.

## Output (2024)

347 rows × 6 columns. The first few signal-firing days:

| day        | close    | ma\_20   | std\_20 | zscore | signal |
| ---------- | -------- | -------- | ------- | ------ | ------ |
| 2024-01-22 | 39568.02 | 43335.61 | 1974.41 | −1.91  | +1     |
| 2024-01-23 | 39897.60 | 43188.22 | 2117.76 | −1.55  | +1     |
| 2024-01-24 | 40084.88 | 42984.91 | 2213.47 | −1.31  | +1     |
| 2024-02-07 | 44349.60 | 42064.01 | 1310.55 | +1.74  | −1     |
| 2024-02-08 | 45288.65 | 42245.49 | 1490.48 | +2.04  | −1     |
| 2024-02-09 | 47132.77 | 42517.32 | 1839.83 | +2.51  | −1     |

Signal counts for the year: **53 long, 99 short, 195 flat**. Short signals dominate because BTC trended upward through 2024 — the rolling mean lagged the price most of the time, pushing the z-score above the upper threshold whenever the rally re-accelerated.

## Extending it

* **Different window or threshold** — change `window_size` and `n_std`. Tighter window (e.g. `10`) reacts faster but flips more often; wider window (`60`) is smoother but slower.
* **Backtest the strategy** — feed `signal` into a cumulative-returns CTE: `signal[t-1] · pct_change(close)[t]` then a running product via `exp(sum(log(1 + ...)))` (see [Maximum drawdown & Calmar](/sql-cookbook/max-drawdown-calmar.md) for the cumprod trick).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.koinju.io/sql-cookbook/mean-reversion-zscore.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.