SPY / QQQ day move in trailing context (~22 sessions)

SELECT
    round(anyIf(cc_pct, ticker = 'QQQ' AND d = toDate('2026-07-09')), 2) AS qqq_close_over_close_pct,
    arrayCount(x -> x > abs(anyIf(cc_pct, ticker = 'QQQ' AND d = toDate('2026-07-09'))), groupArrayIf(abs(cc_pct), ticker = 'QQQ' AND d != toDate('2026-07-09'))) + 1 AS qqq_abs_move_rank,
    countIf(ticker = 'QQQ' AND isFinite(cc_pct)) AS qqq_sessions_compared,
    round(anyIf(oc_pct, ticker = 'SPY' AND d = toDate('2026-07-09')), 2) AS spy_open_to_close_pct,
    arrayCount(x -> x > abs(anyIf(oc_pct, ticker = 'SPY' AND d = toDate('2026-07-09'))), groupArrayIf(abs(oc_pct), ticker = 'SPY' AND d != toDate('2026-07-09'))) + 1 AS spy_abs_move_rank,
    countIf(ticker = 'SPY') AS spy_sessions_compared,
    toString(min(d)) AS first_session
FROM (
    SELECT ticker, d,
           if(isFinite(close_px / lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY d) - 1),
              (close_px / lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY d) - 1) * 100, NULL) AS cc_pct,
           oc_pct
    FROM (
        SELECT ticker, toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               argMax(toFloat64(close), window_start) AS close_px,
               (argMax(toFloat64(close), window_start) / argMin(toFloat64(open), window_start) - 1) * 100 AS oc_pct
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('SPY', 'QQQ')
          AND window_start >= toDateTime('2026-06-08 13:30:00')
          AND window_start < toDateTime('2026-07-10 00:00:00')
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
        GROUP BY ticker, d
    )
)