Where five broad-market ETFs sit inside their own 52-week range

WITH last_session AS (
    SELECT max(date) AS d FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY' AND date >= today() - 12 AND date < today()
),
daily AS (
    SELECT ticker, date AS dt, toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE date < today()
      AND ticker IN ('SPY', 'QQQ', 'DIA', 'IWM', 'RSP')
      AND date > (SELECT d FROM last_session) - 365
),
ranged AS (
    SELECT ticker,
           argMax(c, dt) AS last_close,
           max(c) AS hi,
           min(c) AS lo,
           argMaxIf(c, dt, dt <= toDate('2025-12-31')) AS base_close,
           count() AS n_sessions,
           min(dt) AS first_dt,
           max(dt) AS last_dt
    FROM daily
    GROUP BY ticker
    HAVING n_sessions >= 200
       AND countIf(dt <= toDate('2025-12-31')) > 0
       AND last_dt = (SELECT d FROM last_session)
       AND first_dt <= (SELECT d FROM last_session) - 350
)
SELECT multiIf(ticker = 'SPY', 'S&P 500 (SPY)',
               ticker = 'QQQ', 'Nasdaq 100 (QQQ)',
               ticker = 'DIA', 'Dow 30 (DIA)',
               ticker = 'RSP', 'S&P 500 equal weight (RSP)',
               'Russell 2000 (IWM)') AS label,
       round((1 - last_close / hi) * 100, 2) AS pct_below_high,
       round((last_close / lo - 1) * 100, 1) AS pct_above_low,
       round((last_close / base_close - 1) * 100, 1) AS ytd_return_pct
FROM ranged
ORDER BY pct_below_high ASC