# 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](/compute-engine/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
```

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](/compute-engine/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.


---

# Agent Instructions: 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:

```
GET https://docs.koinju.io/compute-engine/bollinger-bands.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
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.