One time distributions by size, measured against the ex date closing price

SELECT
    multiIf(yield_pct < 1,  'Under 1%',
            yield_pct < 3,  '1% to 3%',
            yield_pct < 5,  '3% to 5%',
            yield_pct < 10, '5% to 10%',
            yield_pct < 25, '10% to 25%',
                            '25% and up')  AS size_bucket,
    count()                                AS payouts,
    countDistinct(ticker)                  AS distinct_tickers
FROM
(
    SELECT
        dv.ticker                                              AS ticker,
        100 * toFloat64(dv.cash_amt) / toFloat64(px.close)      AS yield_pct
    FROM
    (
        SELECT
            ticker,
            date,
            max(close) AS close
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2019-01-01'
          AND (ticker, date) IN
          (
              SELECT ticker, ex_dividend_date
              FROM global_markets.stocks_dividends
              WHERE currency = 'USD'
                AND cash_amount > 0
                AND frequency = 0
          )
        GROUP BY ticker, date
        HAVING close >= 1
    ) AS px
    INNER JOIN
    (
        SELECT
            ticker,
            ex_dividend_date,
            max(cash_amount) AS cash_amt
        FROM global_markets.stocks_dividends
        WHERE currency = 'USD'
          AND cash_amount > 0
          AND frequency = 0
          AND ex_dividend_date >= '2019-01-01'
          AND ex_dividend_date <  today() - 5
        GROUP BY ticker, ex_dividend_date
    ) AS dv ON dv.ticker = px.ticker AND dv.ex_dividend_date = px.date
)
GROUP BY size_bucket
ORDER BY min(yield_pct) ASC