Daily S&P 500 moves by month, measured on SPY

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.

SELECT
    toString(toStartOfMonth(session))                   AS month,
    formatDateTime(toStartOfMonth(session), '%b %Y')    AS month_label,
    round(avg(abs_move_pct), 2)                         AS avg_abs_move_pct,
    round(max(abs_move_pct), 2)                         AS largest_move_pct,
    countIf(abs_move_pct >= 1)                          AS big_move_count,
    count()                                             AS session_count
FROM
(
    SELECT
        session,
        abs(close_px / lagInFrame(close_px) OVER (ORDER BY session ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 1) * 100 AS abs_move_pct
    FROM
    (
        SELECT
            date                   AS session,
            toFloat64(any(close))  AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= toStartOfMonth(today() - 365) - 10
        GROUP BY date
    )
)
WHERE session >= toStartOfMonth(today() - 365)
GROUP BY month, month_label
ORDER BY month