Headline coverage by size of daily move: 36 large caps, May to July 2026

WITH moves AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           (argMax(close, window_start) / argMin(open, window_start) - 1) * 100 AS move_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','TSLA','AVGO','JPM','XOM',
                     'JNJ','WMT','PG','KO','HD','CVX','MRK','PEP','COST','CSCO',
                     'ORCL','CRM','AMD','NFLX','DIS','BA','CAT','IBM','T','VZ',
                     'PFE','NKE','MCD','UNH','BAC','QCOM')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2026-05-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, d
),
headlines AS (
    SELECT arrayJoin(tickers) AS ticker,
           toDate(toTimeZone(published_utc, 'America/New_York')) AS d,
           count() AS articles
    FROM global_markets.stocks_news
    WHERE published_utc >= toDateTime('2026-05-01 04:00:00')
      AND published_utc < toDateTime('2026-08-01 04:00:00')
      AND hasAny(tickers, ['AAPL','MSFT','NVDA','AMZN','GOOGL','META','TSLA','AVGO','JPM','XOM',
                           'JNJ','WMT','PG','KO','HD','CVX','MRK','PEP','COST','CSCO',
                           'ORCL','CRM','AMD','NFLX','DIS','BA','CAT','IBM','T','VZ',
                           'PFE','NKE','MCD','UNH','BAC','QCOM'])
    GROUP BY ticker, d
),
joined AS (
    SELECT multiIf(abs(m.move_pct) >= 5, 'moved 5% or more',
                   abs(m.move_pct) >= 3, 'moved 3% to 5%',
                   abs(m.move_pct) >= 2, 'moved 2% to 3%',
                   abs(m.move_pct) >= 1, 'moved 1% to 2%',
                   'moved under 1%') AS move_bucket,
           multiIf(abs(m.move_pct) >= 5, 5,
                   abs(m.move_pct) >= 3, 4,
                   abs(m.move_pct) >= 2, 3,
                   abs(m.move_pct) >= 1, 2,
                   1) AS bucket_rank,
           ifNull(h.articles, 0) AS articles
    FROM moves AS m
    LEFT JOIN headlines AS h ON m.ticker = h.ticker AND m.d = h.d
)
SELECT move_bucket,
       count() AS ticker_day_count,
       round(100 * countIf(articles > 0) / count(), 1) AS pct_with_headline,
       round(avg(articles), 1) AS avg_headlines
FROM joined
GROUP BY move_bucket, bucket_rank
ORDER BY bucket_rank DESC