2,236 answered market questions

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
),
prior AS (
    SELECT ticker,
           session_date,
           close_px,
           lagInFrame(close_px, 1) OVER (PARTITION BY ticker ORDER BY session_date
                                         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM daily_close
),
breadth AS (
    SELECT session_date,
           round(100 * countIf(close_px > prev_close) / count(), 1) AS advancing_pct
    FROM prior
    WHERE session_date >= toDate('2026-04-01')
      AND prev_close > 0
    GROUP BY session_date
    HAVING count() >= 20
),
combined AS (
    SELECT d.session_date AS session_date,
           round(100 * d.new_highs / d.names_at_extreme, 1) AS record_high_pct,
           b.advancing_pct AS advancing_pct
    FROM daily AS d
    INNER JOIN breadth AS b ON d.session_date = b.session_date
),
smoothed AS (
    SELECT session_date,
           round(avg(record_high_pct) OVER (ORDER BY session_date
                                            ROWS BETWEEN 9 PRECEDING AND CURRENT ROW), 1) AS high_low_index_pct,
           round(avg(advancing_pct) OVER (ORDER BY session_date
                                          ROWS BETWEEN 9 PRECEDING AND CURRENT ROW), 1) AS advancing_pct_10d,
           count() OVER (ORDER BY session_date
                         ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS sessions_in_window
    FROM combined
)
SELECT session_date AS date,
       formatDateTimeInJodaSyntax(session_date, 'MMM d') AS session_label,
       high_low_index_pct,
       advancing_pct_10d
FROM smoothed
WHERE sessions_in_window = 10
ORDER BY session_date