2,170 answered market questions

WITH d AS (
    SELECT argMax(cash_amount, ex_dividend_date) AS latest,
           argMax(frequency, ex_dividend_date) AS freq,
           max(ex_dividend_date) AS last_ex,
           maxIf(ex_dividend_date, ex_dividend_date > today()) AS nxt
    FROM global_markets.stocks_dividends
    WHERE ticker = 'CAT' AND frequency IN (1, 2, 4, 12) AND ex_dividend_date >= today() - 900
),
p AS (
    SELECT argMax(toFloat64(close), window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'CAT' AND window_start >= now() - INTERVAL 8 DAY
)
SELECT round(d.latest, 3) AS latest_dividend,
       multiIf(d.freq = 12, 'monthly', d.freq = 4, 'quarterly', d.freq = 2, 'semi-annual', 'annual') AS frequency,
       round(d.latest * d.freq, 2) AS annual_dividend,
       round(p.price, 2) AS recent_price,
       round(d.latest * d.freq / p.price * 100, 2) AS dividend_yield_pct,
       formatDateTime(d.last_ex, '%b %e, %Y') AS last_ex_date,
       if(d.nxt > today(), formatDateTime(d.nxt, '%b %e, %Y'), 'not yet declared') AS next_ex_date
FROM d CROSS JOIN p