Absolute close-to-close move, 2021 to 2025

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
    bars AS
    (
        SELECT
            ticker,
            date,
            toFloat64(argMax(close, _ingest_time)) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY', 'QQQ', 'NVDA', 'TSLA')
          AND date >= '2020-12-01'
          AND date <  '2026-01-01'
        GROUP BY ticker, date
    ),
    moves AS
    (
        SELECT
            ticker,
            date,
            close_px,
            lagInFrame(close_px, 1) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev_close_px
        FROM bars
    )
SELECT
    ticker,
    round(quantileExact(0.5)(abs_move_pct), 2)           AS median_move_pct,
    round(quantileExact(0.75)(abs_move_pct), 2)          AS p75_move_pct,
    round(quantileExact(0.9)(abs_move_pct), 2)           AS p90_move_pct,
    round(countIf(abs_move_pct >= 4) / count() * 100, 1) AS share_move_over_4_pct
FROM
(
    SELECT
        ticker,
        date,
        abs(close_px / prev_close_px - 1) * 100 AS abs_move_pct
    FROM moves
    WHERE prev_close_px > 0
      AND date >= '2021-01-01'
      AND (toString(ticker), toDate(date)) NOT IN
      (
          SELECT toString(ticker), toDate(execution_date)
          FROM global_markets.stocks_splits
      )
)
GROUP BY ticker
ORDER BY indexOf(['SPY', 'QQQ', 'NVDA', 'TSLA'], ticker)