Apple trailing dividend yield, as-paid sum against split-aware sum

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.

WITH
    monthly_price AS
    (
        SELECT
            toStartOfMonth(date)           AS month_start,
            argMax(toFloat64(close), date) AS month_end_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'AAPL'
          AND date >= '2020-09-01'
          AND date <  '2021-12-01'
        GROUP BY month_start
    ),
    payments AS
    (
        SELECT
            ex_dividend_date                           AS ex_date,
            any(toFloat64(cash_amount))                AS as_paid,
            any(toFloat64(split_adjusted_cash_amount)) AS restated
        FROM global_markets.stocks_dividends
        WHERE ticker = 'AAPL'
          AND ex_dividend_date >= '2019-10-01'
          AND ex_dividend_date <  '2021-12-01'
        GROUP BY ex_dividend_date
    )
SELECT
    toString(p.month_start)                                  AS month,
    round(100 * sum(d.as_paid)  / any(p.month_end_close), 2) AS as_paid_yield_pct,
    round(100 * sum(d.restated) / any(p.month_end_close), 2) AS adjusted_yield_pct
FROM monthly_price AS p
CROSS JOIN payments AS d
WHERE d.ex_date >= addMonths(p.month_start, -11)
  AND d.ex_date <  addMonths(p.month_start, 1)
GROUP BY p.month_start
HAVING count() = 4
ORDER BY p.month_start