> 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/bollinger-bands.md).

# Bollinger Bands

The canonical 20-period, 2σ **Bollinger Bands** in one CTE chain — plus an integer band-cross signal column for trade triggers. Same window-function pattern as [Mean reversion z-score](/sql-cookbook/mean-reversion-zscore.md), just emitted as the conventional `(middle, upper, lower)` triple instead of a z-score.

```
middle_band[t] = mean of close[t-19 … t]
std_20[t]      = population std of close[t-19 … t]   (ddof = 0)
upper_band[t]  = middle_band[t] + 2 · std_20[t]
lower_band[t]  = middle_band[t] − 2 · std_20[t]
signal[t]      = +1 if close < lower_band   (oversold → potential long)
                 −1 if close > upper_band   (overbought → potential short)
                  0 otherwise
```

## Query

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    20 AS window_size,
    2.0 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
    ),
    bands AS (
        SELECT day, close,
               avg(close) OVER w AS middle_band,
               avg(close) OVER w + n_std * stddevPop(close) OVER w AS upper_band,
               avg(close) OVER w - n_std * stddevPop(close) OVER w AS lower_band,
               count(*) OVER w AS valid_rows
        FROM candles
        WINDOW w AS (ORDER BY day ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
    )
SELECT day, close, middle_band, upper_band, lower_band,
       if(close < lower_band, 1, if(close > upper_band, -1, 0)) AS signal
FROM bands
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`/`stddevPop`/`count` OVER)](https://clickhouse.com/docs/sql-reference/window-functions), [`stddevPop`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevpop), [`if`](https://clickhouse.com/docs/sql-reference/functions/conditional-functions#if)

Two SQL details worth flagging:

* **`stddevPop`, not `stddevSamp`.** Canonical Bollinger Bands use population std (`ddof = 0`); TA-Lib's `BBANDS()` follows that. Pandas' `.rolling(N).std()` defaults to sample std, so the reproducible Python uses `.rolling(20).std(ddof=0)` explicitly. Mixing pop / sample changes the band width by `√(N / (N − 1)) ≈ 1.026` for `N = 20`.
* **`count(*) OVER w = window_size`** filters rows where the rolling window hasn't yet filled — same role as `dropna()` after pandas `.rolling(20)`.

## Output (2024)

347 rows × 6 columns. **16 long signals, 28 short signals, 303 flat** across the year — short-heavy because BTC repeatedly broke through the upper band on the way up. Sample rows showing all three signal values:

| day        | close     | middle\_band | upper\_band | lower\_band | signal |
| ---------- | --------- | ------------ | ----------- | ----------- | ------ |
| 2024-02-08 | 45288.65  | 42245.49     | 45150.97    | 39340.01    | −1     |
| 2024-02-13 | 49699.59  | 44244.18     | 49712.15    | 38776.21    | 0      |
| 2024-04-12 | 67116.52  | 69110.15     | 72347.79    | 65872.51    | 0      |
| 2024-04-13 | 63924.51  | 68945.88     | 72822.79    | 65068.97    | +1     |
| 2024-04-15 | 63419.99  | 68406.57     | 73069.12    | 63744.02    | +1     |
| 2024-08-05 | 54018.81  | 64803.52     | 71971.54    | 57635.50    | +1     |
| 2024-12-16 | 106058.66 | 98941.58     | 104757.22   | 93125.94    | −1     |

## Extending it

* **Different window or σ width** — change `window_size` and `n_std`. Common variants: `(10, 1.5)` for tighter bands, `(50, 2.5)` for wider/slower.
* **Z-score view** — same numbers, different presentation: `(close - middle_band) / stddevPop(close) OVER w` is the [z-score](/sql-cookbook/mean-reversion-zscore.md) and it crosses ±`n_std` at exactly the same rows where `close` crosses the bands.
* **Other indicators in the same CTE chain** — RSI, MACD, Donchian channels all decompose to one or two window functions each. Extend `bands` with extra columns rather than another CTE.