AAPL realized volatility: 20-session against 60-session lookback

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    daily AS
    (
        SELECT
            date             AS session_date,
            toFloat64(close) AS close_px,
            lagInFrame(toFloat64(close)) OVER
                (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'AAPL'
          AND date >= today() - 400
          AND date <  today()
    ),
    returns AS
    (
        SELECT
            session_date,
            log(close_px / prev_close)                AS r,
            row_number() OVER (ORDER BY session_date) AS n
        FROM daily
        WHERE prev_close > 0
    ),
    rolling AS
    (
        SELECT
            session_date,
            n,
            round(100 * sqrt(252) * stddevSamp(r) OVER
                (ORDER BY n ROWS BETWEEN 19 PRECEDING AND CURRENT ROW), 1) AS hv_20d_pct,
            round(100 * sqrt(252) * stddevSamp(r) OVER
                (ORDER BY n ROWS BETWEEN 59 PRECEDING AND CURRENT ROW), 1) AS hv_60d_pct
        FROM returns
    )
SELECT
    toString(session_date) AS date,
    hv_20d_pct,
    hv_60d_pct
FROM rolling
WHERE n >= 60
  AND (n % 3) = 0
ORDER BY date