3,256 answered market questions

WITH
    (
        SELECT toFloat64(argMax(cash_amount, ex_dividend_date))
        FROM global_markets.stocks_dividends
        WHERE ticker = 'PEP'
    ) AS declared_rate,
    (
        SELECT round(toFloat64(argMax(close, date)), 2)
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'PEP'
          AND date >= today() - 45
    ) AS recent_close,
    (
        SELECT formatDateTime(max(date), '%b %e, %Y')
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'PEP'
          AND date >= today() - 45
    ) AS as_of_label
SELECT
    round(recent_close * (0.80 + 0.05 * n), 2)                             AS price_level,
    round(4 * declared_rate, 4)                                            AS annual_rate,
    round(100 * 4 * declared_rate / (recent_close * (0.80 + 0.05 * n)), 2) AS yield_pct,
    as_of_label                                                            AS as_of
FROM
(
    SELECT arrayJoin(range(9)) AS n
)
ORDER BY price_level