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, 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

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