Trailing yield by payment schedule: liquid US-listed payers, twelve months of cash through June 2026

WITH sched AS (
    SELECT ticker,
           argMax(frequency, ex_dividend_date) AS freq,
           sum(cash_amount) AS ttm_cash
    FROM global_markets.stocks_dividends
    WHERE cash_amount > 0
      AND distribution_type = 'recurring'
      AND ex_dividend_date >= '2025-07-01'
      AND ex_dividend_date <= '2026-06-30'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    HAVING freq IN (1, 2, 4, 12)
),
tape AS (
    SELECT ticker,
           argMax(toFloat64(close), window_start) AS last_close,
           sum(toFloat64(close) * volume) AS dollar_vol
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN (SELECT ticker FROM sched)
      AND window_start >= toDateTime('2026-06-01 00:00:00')
      AND window_start < toDateTime('2026-07-01 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
    HAVING last_close > 5 AND dollar_vol >= 20000000
)
SELECT multiIf(freq = 12, 'Monthly',
               freq = 4, 'Quarterly',
               freq = 2, 'Twice a year',
               'Once a year') AS schedule,
       count() AS tickers,
       round(quantileDeterministic(0.5)(100 * ttm_cash / last_close, cityHash64(ticker)), 2) AS median_trailing_yield_pct,
       round(quantileDeterministic(0.9)(100 * ttm_cash / last_close, cityHash64(ticker)), 2) AS p90_trailing_yield_pct
FROM sched
INNER JOIN tape USING (ticker)
GROUP BY schedule
ORDER BY median_trailing_yield_pct DESC