Latest declared dividend per share for each sample holding

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 holdings AS
(
    SELECT
        tupleElement(h, 1) AS ticker,
        tupleElement(h, 2) AS shares,
        tupleElement(h, 3) AS cost_per_share
    FROM
    (
        SELECT arrayJoin([
            ('KO',   200.0,  54.10),
            ('PG',    60.0, 141.25),
            ('JNJ',   50.0, 155.40),
            ('MSFT',  40.0, 268.75),
            ('XOM',   80.0,  96.20),
            ('O',    150.0,  61.85)
        ]) AS h
    )
),
declared AS
(
    SELECT
        ticker,
        argMax(cash_amount, ex_dividend_date)              AS cash_per_payment,
        argMax(frequency, ex_dividend_date)                AS pays_per_year,
        formatDateTime(max(ex_dividend_date), '%b %e, %Y') AS last_ex_label
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'PG', 'JNJ', 'MSFT', 'XOM', 'O')
      AND currency = 'USD'
      AND frequency IN (1, 2, 4, 12)
      AND ex_dividend_date >= today() - 400
      AND ex_dividend_date <= today() + 120
    GROUP BY ticker
)
SELECT
    h.ticker                                                    AS ticker,
    round(toFloat64(d.cash_per_payment), 4)                     AS cash_per_payment,
    round(toFloat64(d.cash_per_payment) * d.pays_per_year, 2)   AS annual_per_share,
    toUInt16(d.pays_per_year)                                   AS payment_count_per_year,
    d.last_ex_label                                             AS last_declared
FROM holdings AS h
INNER JOIN declared AS d ON d.ticker = h.ticker
ORDER BY payment_count_per_year DESC, annual_per_share DESC