How far seven markets travel between Tuesday's close and Friday 3:30 pm ET, Aug 2024 to Jul 2026

WITH raw AS (
    SELECT ticker,
           toTimeZone(window_start, 'America/New_York') AS et,
           close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'QQQ', 'TLT', 'GLD', 'SLV', 'USO', 'UNG')
      AND window_start >= toDateTime('2024-08-01 00:00:00')
      AND window_start < toDateTime('2026-08-01 00:00:00')
),
bars AS (
    SELECT ticker,
           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 ticker,
           toMonday(session_date) AS week_start,
           argMax(close, minute_of_day) AS tuesday_close
    FROM bars
    WHERE day_of_week = 2
    GROUP BY ticker, week_start
),
friday_release AS (
    SELECT ticker,
           toMonday(session_date) AS week_start,
           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 ticker, week_start
)
SELECT t.ticker AS ticker,
       count() AS weeks_counted,
       round(quantileDeterministic(0.5)(abs(toFloat64(f.friday_1530) / toFloat64(t.tuesday_close) - 1) * 100,
                                        cityHash64(toString(t.week_start))), 2) AS median_abs_move_pct,
       round(quantileDeterministic(0.9)(abs(toFloat64(f.friday_1530) / toFloat64(t.tuesday_close) - 1) * 100,
                                        cityHash64(toString(t.week_start))), 2) AS p90_abs_move_pct,
       round(100 * countIf(abs(toFloat64(f.friday_1530) / toFloat64(t.tuesday_close) - 1) * 100 >= 2) / count(), 1) AS pct_weeks_over_2pct
FROM tuesday_snapshot AS t
INNER JOIN friday_release AS f
        ON t.ticker = f.ticker AND t.week_start = f.week_start
GROUP BY t.ticker
ORDER BY median_abs_move_pct DESC