Yield distribution across liquid monthly payers, with each band's median 12-month price change

WITH monthly AS (
    SELECT ticker, argMax(cash_amount, ex_dividend_date) AS latest_payment
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date > today() - 365
      AND ex_dividend_date <= today()
      AND cash_amount > 0
      AND distribution_type = 'recurring'
      AND ticker NOT IN ('SPCX')
      AND ticker NOT IN ('KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 400 AND today())
    GROUP BY ticker
    HAVING count() BETWEEN 10 AND 14
       AND argMax(frequency, ex_dividend_date) = 12
),
filers AS (
    SELECT ticker
    FROM global_markets.stocks_ratios
    GROUP BY ticker
    HAVING argMax(market_cap, date) > 0
),
tape AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           sum(toFloat64(close) * volume) AS dollar_vol,
           argMax(toFloat64(close), window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN (SELECT ticker FROM monthly)
      AND (window_start >= toDateTime(today() - 32)
           OR (window_start >= toDateTime(today() - 378) AND window_start < toDateTime(today() - 358)))
      AND toDate(toTimeZone(window_start, 'America/New_York')) < today()
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    GROUP BY ticker, d
),
px AS (
    SELECT ticker,
           avgIf(dollar_vol, d > today() - 32) AS adv_usd,
           argMaxIf(close_px, d, d > today() - 32) AS last_close,
           argMaxIf(close_px, d, d <= today() - 358) AS close_year_ago,
           countIf(d > today() - 32) AS recent_sessions,
           countIf(d <= today() - 358) AS old_sessions
    FROM tape
    GROUP BY ticker
    HAVING recent_sessions >= 15
       AND old_sessions > 0
       AND adv_usd >= 1000000
       AND last_close > 0
       AND close_year_ago > 0
),
named AS (
    SELECT m.ticker AS ticker,
           100 * m.latest_payment * 12 / p.last_close AS yld,
           100 * (p.last_close - p.close_year_ago) / p.close_year_ago AS price_change,
           m.ticker IN (SELECT ticker FROM filers) AS is_filer
    FROM monthly m
    INNER JOIN px p ON p.ticker = m.ticker
)
SELECT multiIf(yld < 3, 'Under 3%',
               yld < 5, 'From 3% to 5%',
               yld < 8, 'From 5% to 8%',
               yld < 12, 'From 8% to 12%',
               'Above 12%') AS bucket,
       count() AS payers,
       round(quantileDeterministic(0.5)(yld, cityHash64(ticker)), 2) AS median_yield_pct,
       round(quantileDeterministic(0.5)(price_change, cityHash64(ticker)), 1) AS median_price_change_12m_pct,
       countIf(is_filer) AS company_filings
FROM named
GROUP BY bucket
ORDER BY median_yield_pct