Median move before and after European markets close, first half of 2026

WITH bars AS
(
    SELECT
        ticker,
        window_start,
        toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
        toHour(toTimeZone(window_start, 'America/New_York')) * 60
            + toMinute(toTimeZone(window_start, 'America/New_York')) AS et_minute,
        toFloat64(open)  AS px_o,
        toFloat64(close) AS px_c
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'VGK', 'EFA', 'EWJ')
      AND window_start >= '2026-01-02 00:00:00'
      AND window_start <  '2026-07-01 00:00:00'
),
legs AS
(
    SELECT
        ticker,
        session_date,
        argMinIf(px_o, window_start, et_minute >= 570) AS px_open,
        argMaxIf(px_c, window_start, et_minute <  690) AS px_midday,
        argMaxIf(px_c, window_start, et_minute <  960) AS px_close
    FROM bars
    WHERE et_minute >= 570 AND et_minute < 960
    GROUP BY ticker, session_date
    HAVING px_open > 0 AND px_midday > 0 AND px_close > 0
)
SELECT
    ticker,
    round(quantileExact(0.5)(abs(px_midday / px_open  - 1) * 100), 3) AS morning_move_pct,
    round(quantileExact(0.5)(abs(px_close  / px_midday - 1) * 100), 3) AS afternoon_move_pct
FROM legs
GROUP BY ticker
ORDER BY multiIf(ticker = 'SPY', 1, ticker = 'VGK', 2, ticker = 'EFA', 3, 4)