S&P 500 ETF path on August 5, 2024, measured against the prior close

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
    reference AS
    (
        SELECT any(toFloat64(close)) AS prior_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date = '2024-08-02'
    )
SELECT
    formatDateTime(toStartOfInterval(toTimeZone(m.window_start, 'America/New_York'), INTERVAL 10 MINUTE), '%H:%i') AS et_time,
    round(100 * (toFloat64(argMax(m.close, m.window_start)) / any(r.prior_close) - 1), 2)        AS pct_vs_prior_close,
    round(100 * (toFloat64(max(m.high)) - toFloat64(min(m.low))) / any(r.prior_close), 2)        AS bucket_range_pct
FROM global_markets.delayed_stocks_minute_aggs AS m
CROSS JOIN reference AS r
WHERE m.ticker = 'SPY'
  AND m.window_start >= '2024-08-05 13:30:00'
  AND m.window_start <  '2024-08-05 20:00:00'
  AND m.volume > 0
GROUP BY et_time
ORDER BY et_time ASC