3,256 answered market questions

WITH
    daily AS
    (
        SELECT
            date,
            toFloat64(any(close)) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2025-06-20'
          AND date <  '2026-07-01'
        GROUP BY date
    ),
    seq AS
    (
        SELECT *, row_number() OVER (ORDER BY date) AS n
        FROM daily
    ),
    steps AS
    (
        SELECT
            toStartOfMonth(b.date)      AS m,
            b.close_px / a.close_px - 1 AS ret
        FROM seq AS a
        INNER JOIN seq AS b ON b.n = a.n + 1
        WHERE b.date >= '2025-07-01'
    )
SELECT
    formatDateTime(m, '%Y-%m')             AS month,
    formatDateTime(m, '%b %Y')             AS month_label,
    round(100 * sqrt(sum(pow(ret, 2))), 2) AS path_move_pct,
    round(100 * abs(sum(ret)), 2)          AS net_move_pct
FROM steps
GROUP BY m
ORDER BY m