C 股利殖利率與除息日查詢

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 = 'C' 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 = 'C' 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

SELECT toDate(ex_dividend_date) AS date,
       round(cash_amount, 4) AS dividend_per_share
FROM global_markets.stocks_dividends
WHERE ticker = 'C' AND frequency IN (1, 2, 4, 12) AND ex_dividend_date >= today() - 1100
ORDER BY ex_dividend_date DESC
LIMIT 10

SELECT toString(toYear(ex_dividend_date)) AS year,
       round(sum(cash_amount), 2) AS dividends_paid_per_share
FROM global_markets.stocks_dividends
WHERE ticker = 'C' AND frequency IN (1, 2, 4, 12)
  AND ex_dividend_date >= toDate('2019-01-01') AND ex_dividend_date < toDate(concat(toString(toYear(today())), '-01-01'))
GROUP BY year
ORDER BY year