The largest single session against a typical one, by name

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
    symbol,
    round(quantileDeterministic(0.5)(abs_move_pct, toUInt32(session_date)), 2) AS typical_abs_move_pct,
    round(max(abs_move_pct), 2)                                               AS largest_abs_move_pct,
    round(max(abs_move_pct) / quantileDeterministic(0.5)(abs_move_pct, toUInt32(session_date)), 1) AS largest_to_typical_ratio,
    formatDateTime(argMax(session_date, abs_move_pct), '%b %e, %Y')           AS largest_move_on
FROM
(
    SELECT
        symbol,
        session_date,
        abs(close_px / lagInFrame(close_px) OVER (PARTITION BY symbol ORDER BY session_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1) * 100 AS abs_move_pct
    FROM
    (
        SELECT
            ticker                AS symbol,
            date                  AS session_date,
            toFloat64(max(close)) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY', 'AAPL', 'MSFT', 'NVDA', 'AMZN', 'KO')
          AND date >= today() - 400
          AND date <  today() - 1
        GROUP BY symbol, session_date
    )
)
WHERE isFinite(abs_move_pct)
  AND abs_move_pct > 0
GROUP BY symbol
ORDER BY largest_to_typical_ratio DESC