How each yield band got there: median price and dividend change over the prior 12 months

WITH px_base AS (
    SELECT ticker,
           argMax(toFloat64(close), window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2025-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker
    HAVING price >= 5
),
px_prior AS (
    SELECT ticker,
           argMax(toFloat64(close), window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2024-06-28')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker
    HAVING price > 0
),
rate_base AS (
    SELECT ticker,
           argMax(toFloat64(cash_amount) * frequency, ex_dividend_date) AS annual_rate
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND ex_dividend_date > toDate('2024-06-30')
      AND ex_dividend_date <= toDate('2025-06-30')
    GROUP BY ticker
),
rate_prior AS (
    SELECT ticker,
           argMax(toFloat64(cash_amount) * frequency, ex_dividend_date) AS annual_rate
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND ex_dividend_date > toDate('2023-06-30')
      AND ex_dividend_date <= toDate('2024-06-28')
    GROUP BY ticker
)
SELECT multiIf(100 * rb.annual_rate / b.price >= 8, '8% and up',
               100 * rb.annual_rate / b.price >= 6, '6-8%',
               100 * rb.annual_rate / b.price >= 4, '4-6%',
               100 * rb.annual_rate / b.price >= 2, '2-4%',
               'under 2%') AS yield_band,
       count() AS names,
       round(quantileDeterministic(0.5)(100 * rb.annual_rate / b.price, cityHash64(b.ticker)), 2) AS median_yield_pct,
       round(quantileDeterministic(0.5)(100 * (b.price / p.price - 1), cityHash64(b.ticker)), 1) AS median_price_change_pct,
       round(quantileDeterministic(0.5)(100 * (rb.annual_rate / rp.annual_rate - 1), cityHash64(b.ticker)), 1) AS median_dividend_change_pct
FROM px_base AS b
INNER JOIN px_prior AS p ON b.ticker = p.ticker
INNER JOIN rate_base AS rb ON b.ticker = rb.ticker
INNER JOIN rate_prior AS rp ON b.ticker = rp.ticker
GROUP BY yield_band
ORDER BY median_yield_pct