2,170 answered market questions

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           toFloat64(argMax(close, window_start)) AS px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'KO', 'JNJ', 'AAPL', 'MSFT', 'NVDA', 'TSLA')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2025-07-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, d
),
moves AS (
    SELECT ticker, d,
           100 * (px / any(px) OVER (PARTITION BY ticker ORDER BY d
                                     ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) - 1) AS move_pct
    FROM daily
)
SELECT ticker,
       count() AS sessions,
       round(quantileDeterministic(0.5)(abs(move_pct), cityHash64(ticker, d)), 2) AS median_abs_move_pct,
       round(quantileDeterministic(0.95)(abs(move_pct), cityHash64(ticker, d)), 2) AS p95_abs_move_pct,
       round(100 * countIf(abs(move_pct) >= 3) / count(), 1) AS pct_days_beyond_3,
       round(min(move_pct), 2) AS worst_day_pct
FROM moves
WHERE isFinite(move_pct)
GROUP BY ticker
ORDER BY p95_abs_move_pct