Trailing yield by sector fund: twelve months of distributions over the late-July 2026 price

WITH px AS (
    SELECT ticker,
           argMax(close, window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('XLU','XLRE','XLP','XLE','XLF','XLV','XLB','XLI','XLC','XLY','XLK')
      AND window_start >= toDateTime('2026-07-20 00:00:00')
      AND window_start < toDateTime('2026-07-31 00: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
),
dv AS (
    SELECT ticker,
           sum(cash_amount) AS ttm_distributions
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('XLU','XLRE','XLP','XLE','XLF','XLV','XLB','XLI','XLC','XLY','XLK')
      AND cash_amount > 0
      AND ex_dividend_date > toDate('2025-07-30')
      AND ex_dividend_date <= toDate('2026-07-30')
    GROUP BY ticker
)
SELECT transform(px.ticker,
                 ['XLU','XLRE','XLP','XLE','XLF','XLV','XLB','XLI','XLC','XLY','XLK'],
                 ['Utilities','Real estate','Consumer staples','Energy','Financials',
                  'Health care','Materials','Industrials','Communications',
                  'Consumer discretionary','Technology'],
                 'Other') AS sector,
       px.ticker AS fund_ticker,
       round(dv.ttm_distributions, 3) AS annual_distributions_usd,
       round(dv.ttm_distributions / px.price * 100, 2) AS trailing_yield_pct
FROM px
INNER JOIN dv ON px.ticker = dv.ticker
ORDER BY trailing_yield_pct DESC