Rolling Windows — Moving Averages, Bollinger Bands & Momentum

Moving averages smooth noise to reveal trends. Bollinger Bands add standard deviation envelopes to detect when a metric is behaving abnormally. Momentum measures rate of change. All of these are pure SQL window functions.

-- 20-day moving average, Bollinger Bands, and momentum for AAPL
WITH daily AS (
    SELECT
        ticker,
        price_date,
        close_cents,
        -- Simple Moving Averages
        AVG(close_cents) OVER (
            PARTITION BY ticker
            ORDER BY price_date
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        )                                               AS sma_20,
        AVG(close_cents) OVER (
            PARTITION BY ticker
            ORDER BY price_date
            ROWS BETWEEN 49 PRECEDING AND CURRENT ROW
        )                                               AS sma_50,
        -- Standard deviation for Bollinger Bands
        STDDEV_SAMP(close_cents) OVER (
            PARTITION BY ticker
            ORDER BY price_date
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        )                                               AS std_20,
        -- Row count to exclude partial windows
        COUNT(*) OVER (
            PARTITION BY ticker
            ORDER BY price_date
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        )                                               AS window_rows
    FROM ts_stock_prices
    WHERE ticker = 'AAPL'
)
SELECT
    price_date,
    ROUND(close_cents / 100.0, 2)                       AS close_usd,
    ROUND(sma_20 / 100.0, 2)                            AS sma_20_usd,
    ROUND(sma_50 / 100.0, 2)                            AS sma_50_usd,
    -- Bollinger Bands: mean ± 2 standard deviations
    ROUND((sma_20 + 2 * std_20) / 100.0, 2)            AS upper_band,
    ROUND((sma_20 - 2 * std_20) / 100.0, 2)            AS lower_band,
    -- Price relative to bands (0 = lower, 1 = upper)
    ROUND((close_cents - (sma_20 - 2 * std_20))
          / NULLIF(4 * std_20, 0), 3)                   AS band_position,
    CASE
        WHEN close_cents > sma_20 + 2 * std_20 THEN 'Overbought'
        WHEN close_cents < sma_20 - 2 * std_20 THEN 'Oversold'
        WHEN close_cents > sma_20               THEN 'Above SMA'
        ELSE                                         'Below SMA'
    END                                                 AS signal
FROM daily
WHERE window_rows = 20
ORDER BY price_date DESC
LIMIT 30;