2,173 answered market questions

WITH spy AS
(
    SELECT
        date,
        toFloat64(close) AS close_px,
        lagInFrame(toFloat64(close)) OVER
            (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2019-01-01'
      AND date <  '2026-08-01'
)
SELECT
    toString(toStartOfMonth(date))                AS month,
    formatDateTime(toStartOfMonth(date), '%b %Y') AS month_label,
    round(quantileDeterministic(0.50)(abs(close_px / prev_close - 1) * 100,
                                      toUInt64(toUnixTimestamp(date))), 2) AS median_move_pct,
    round(max(abs(close_px / prev_close - 1) * 100), 2)                    AS largest_move_pct
FROM spy
WHERE prev_close > 0
GROUP BY month, month_label
ORDER BY month