2,173 answered market questions

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMax(close, window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2016-01-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2025-12-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
),
yr AS (
    SELECT toYear(d) AS year,
           argMin(close_px, d) AS first_close,
           argMax(close_px, d) AS last_close,
           max(close_px) AS high_close,
           min(close_px) AS low_close,
           count() AS sessions
    FROM daily
    GROUP BY year
)
SELECT year,
       sessions,
       round(100 * (toFloat64(last_close) / toFloat64(first_close) - 1), 1) AS price_return_pct,
       round(100 * (toFloat64(high_close) / toFloat64(low_close) - 1), 1) AS high_low_range_pct
FROM yr
ORDER BY year