Annual dividend income per position, with a running portfolio total

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
    FROM
    (
        SELECT arrayJoin([
            ('KO',   200.0),
            ('PG',    60.0),
            ('JNJ',   50.0),
            ('MSFT',  40.0),
            ('XOM',   80.0),
            ('O',    150.0)
        ]) 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
    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
    ticker,
    annual_income,
    round(sum(annual_income) OVER (ORDER BY annual_income DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS running_total
FROM
(
    SELECT
        h.ticker AS ticker,
        round(toFloat64(d.cash_per_payment) * d.pays_per_year * h.shares, 2) AS annual_income
    FROM holdings AS h
    INNER JOIN declared AS d ON d.ticker = h.ticker
)
ORDER BY annual_income DESC