How big each kind of cash distribution is, as a share of the stock price

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

SELECT
    multiIf(frequency = 0, 'One time',
            frequency = 1, 'Annual',
            frequency = 2, 'Semiannual',
            frequency = 4, 'Quarterly',
                           'Monthly')                     AS payout_type,
    count()                                               AS payouts,
    round(quantileDeterministic(0.5)(yield_pct, det), 3)  AS median_yield_pct,
    round(quantileDeterministic(0.9)(yield_pct, det), 2)  AS p90_yield_pct
FROM
(
    SELECT
        dv.frequency                                            AS frequency,
        100 * toFloat64(dv.cash_amt) / toFloat64(px.close)       AS yield_pct,
        cityHash64(dv.ticker, toString(dv.ex_dividend_date))     AS det
    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
          )
        GROUP BY ticker, date
        HAVING close >= 1
    ) AS px
    INNER JOIN
    (
        SELECT
            ticker,
            ex_dividend_date,
            frequency,
            max(cash_amount) AS cash_amt
        FROM global_markets.stocks_dividends
        WHERE currency = 'USD'
          AND cash_amount > 0
          AND frequency IN (0, 1, 2, 4, 12)
          AND ex_dividend_date >= '2019-01-01'
          AND ex_dividend_date <  today() - 5
        GROUP BY ticker, ex_dividend_date, frequency
    ) AS dv ON dv.ticker = px.ticker AND dv.ex_dividend_date = px.date
)
GROUP BY payout_type
HAVING payouts >= 200
ORDER BY (payout_type = 'One time') ASC, median_yield_pct ASC