Payout ratio and dividend action by yield band: US payers over $2B, latest snapshot

WITH snapshot AS (
    SELECT ticker,
           toFloat64(dividend_yield) * 100 AS yield_pct,
           toFloat64(dividend_yield) * toFloat64(price) AS annual_dividend,
           toFloat64(earnings_per_share) AS eps
    FROM global_markets.stocks_ratios
    WHERE date = (SELECT max(date) FROM global_markets.stocks_ratios)
      AND price >= 5
      AND market_cap >= 2000000000
      AND dividend_yield > 0
      AND earnings_per_share IS NOT NULL
),
payments AS (
    SELECT ticker,
           sumIf(toFloat64(cash_amount), ex_dividend_date > today() - INTERVAL 1 YEAR) AS ttm_paid,
           sumIf(toFloat64(cash_amount), ex_dividend_date <= today() - INTERVAL 1 YEAR
                 AND ex_dividend_date > today() - INTERVAL 2 YEAR) AS prior_paid
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date > today() - INTERVAL 2 YEAR
      AND ex_dividend_date <= today()
    GROUP BY ticker
    HAVING ttm_paid > 0 AND prior_paid > 0
)
SELECT multiIf(s.yield_pct >= 8, '8% and up',
               s.yield_pct >= 5, '5-8%',
               s.yield_pct >= 2.5, '2.5-5%',
               'under 2.5%') AS yield_band,
       count() AS companies,
       round(quantileDeterministic(0.5)(s.yield_pct, cityHash64(s.ticker)), 2) AS median_yield_pct,
       round(quantileDeterministicIf(0.5)(s.annual_dividend / s.eps * 100,
                                          cityHash64(s.ticker), s.eps > 0), 0) AS median_payout_ratio,
       round(100 * countIf(p.ttm_paid < p.prior_paid * 0.99) / count(), 1) AS pct_paid_less_than_year_before,
       round(100 * countIf(p.ttm_paid > p.prior_paid * 1.01) / count(), 1) AS pct_paid_more_than_year_before
FROM snapshot AS s
INNER JOIN payments AS p ON s.ticker = p.ticker
GROUP BY yield_band
HAVING countIf(s.eps > 0) > 0
ORDER BY median_yield_pct