SPY: the move from Tuesday's close to Friday 3:30 pm ET, week by week, Aug 2025 to Jul 2026

WITH raw AS (
    SELECT toTimeZone(window_start, 'America/New_York') AS et,
           close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2025-08-01 00:00:00')
      AND window_start < toDateTime('2026-08-01 00:00:00')
),
bars AS (
    SELECT toDate(et) AS session_date,
           toDayOfWeek(et) AS day_of_week,
           toHour(et) * 60 + toMinute(et) AS minute_of_day,
           close
    FROM raw
    WHERE toDayOfWeek(et) IN (2, 5)
      AND (toHour(et) * 60 + toMinute(et)) BETWEEN 570 AND 959
),
tuesday_snapshot AS (
    SELECT toMonday(session_date) AS week_start,
           argMax(close, minute_of_day) AS tuesday_close
    FROM bars
    WHERE day_of_week = 2
    GROUP BY week_start
),
friday_release AS (
    SELECT toMonday(session_date) AS week_start,
           max(session_date) AS friday_date,
           argMax(close, minute_of_day) AS friday_1530
    FROM bars
    WHERE day_of_week = 5
      AND minute_of_day BETWEEN 900 AND 930
    GROUP BY week_start
)
SELECT f.friday_date AS week,
       formatDateTime(f.friday_date, '%b %e, %Y') AS friday_label,
       round((toFloat64(f.friday_1530) / toFloat64(t.tuesday_close) - 1) * 100, 2) AS change_pct,
       round(abs(toFloat64(f.friday_1530) / toFloat64(t.tuesday_close) - 1) * 100, 2) AS abs_change_pct
FROM tuesday_snapshot AS t
INNER JOIN friday_release AS f ON t.week_start = f.week_start
ORDER BY week