Record High Percent and its 10-day average (the High-Low Index), 40-name basket

WITH daily_close AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           argMax(close, window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','AVGO','JPM','JNJ','XOM','PG',
                     'KO','PEP','WMT','HD','CVX','MRK','PFE','ABBV','CSCO','ORCL',
                     'CRM','ADBE','MCD','NKE','VZ','T','DIS','BA','CAT','GE',
                     'IBM','MMM','UNH','LLY','COST','TGT','SBUX','GS','MS','LIN')
      AND window_start >= toDateTime('2025-03-01 00:00:00')
      AND window_start < toDateTime('2026-08-01 05:00:00')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, session_date
),
extremes AS (
    SELECT cur.ticker AS ticker,
           cur.session_date AS session_date,
           cur.close_px AS close_px,
           max(hist.close_px) AS high_52w,
           min(hist.close_px) AS low_52w
    FROM daily_close AS cur
    INNER JOIN daily_close AS hist ON cur.ticker = hist.ticker
    WHERE cur.session_date >= toDate('2026-04-01')
      AND hist.session_date <= cur.session_date
      AND hist.session_date > cur.session_date - 364
    GROUP BY cur.ticker, cur.session_date, cur.close_px
),
daily AS (
    SELECT session_date,
           countIf(close_px >= high_52w) AS new_highs,
           countIf(close_px >= high_52w) + countIf(close_px <= low_52w) AS names_at_extreme
    FROM extremes
    GROUP BY session_date
    HAVING names_at_extreme > 0
),
rhp AS (
    SELECT session_date,
           round(100 * new_highs / names_at_extreme, 1) AS record_high_pct
    FROM daily
),
smoothed AS (
    SELECT session_date,
           record_high_pct,
           round(avg(record_high_pct) OVER (ORDER BY session_date
                                            ROWS BETWEEN 9 PRECEDING AND CURRENT ROW), 1) AS high_low_index_pct,
           count() OVER (ORDER BY session_date
                         ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS sessions_in_window
    FROM rhp
)
SELECT session_date AS date,
       formatDateTimeInJodaSyntax(session_date, 'MMM d') AS session_label,
       record_high_pct,
       high_low_index_pct
FROM smoothed
WHERE sessions_in_window = 10
ORDER BY session_date