Liquid monthly payers grouped by the change in their monthly rate over three years, with each group's current yield

WITH monthly AS (
    SELECT ticker,
           argMaxIf(cash_amount, ex_dividend_date, ex_dividend_date >= '2025-07-01') AS recent_rate,
           argMinIf(cash_amount, ex_dividend_date, ex_dividend_date <= '2023-12-31') AS early_rate
    FROM global_markets.stocks_dividends
    WHERE cash_amount > 0
      AND distribution_type = 'recurring'
      AND ex_dividend_date >= '2023-07-01'
      AND ex_dividend_date <= '2026-06-30'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    HAVING countIf(ex_dividend_date >= '2025-07-01') BETWEEN 10 AND 14
       AND argMax(frequency, ex_dividend_date) = 12
       AND countIf(ex_dividend_date <= '2023-12-31') >= 4
       AND early_rate > 0
),
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 monthly)
      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 > 0 AND dollar_vol >= 20000000
)
SELECT multiIf(100 * (recent_rate - early_rate) / early_rate < -5, 'Rate cut',
               100 * (recent_rate - early_rate) / early_rate <= 5, 'Rate held flat',
               100 * (recent_rate - early_rate) / early_rate <= 25, 'Rate raised up to 25%',
               'Rate raised over 25%') AS rate_direction,
       count() AS payers,
       round(100 * count() / sum(count()) OVER (), 1) AS share_of_group_pct,
       round(quantileDeterministic(0.5)(100 * recent_rate * 12 / last_close, cityHash64(ticker)), 2) AS median_yield_pct,
       round(quantileDeterministic(0.5)(100 * (recent_rate - early_rate) / early_rate, cityHash64(ticker)), 1) AS median_rate_change_pct
FROM monthly
INNER JOIN tape USING (ticker)
GROUP BY rate_direction
ORDER BY median_rate_change_pct