3,256 answered market questions

WITH holdings AS
(
    SELECT
        tupleElement(h, 1) AS ticker,
        tupleElement(h, 2) AS cost_per_share
    FROM
    (
        SELECT arrayJoin([
            ('KO',    54.10),
            ('PG',   141.25),
            ('JNJ',  155.40),
            ('MSFT', 268.75),
            ('XOM',   96.20),
            ('O',     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
    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
),
last_px AS
(
    SELECT
        ticker,
        argMax(toFloat64(close), date) AS last_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('KO', 'PG', 'JNJ', 'MSFT', 'XOM', 'O')
      AND date >= today() - 45
    GROUP BY ticker
)
SELECT
    h.ticker AS ticker,
    round(toFloat64(d.cash_per_payment) * d.pays_per_year / h.cost_per_share * 100, 2) AS yield_on_cost_pct,
    round(toFloat64(d.cash_per_payment) * d.pays_per_year / p.last_close * 100, 2)     AS current_yield_pct
FROM holdings AS h
INNER JOIN declared AS d ON d.ticker = h.ticker
INNER JOIN last_px  AS p ON p.ticker = h.ticker
ORDER BY yield_on_cost_pct DESC