2,401 answered market questions

WITH px AS (
    SELECT toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York'))) AS month_start,
           argMax(toFloat64(close), window_start) AS price,
           max(toDate(toTimeZone(window_start, 'America/New_York'))) AS last_day
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'CAG'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2021-08-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY month_start
),
dv AS (
    SELECT ex_dividend_date, toFloat64(cash_amount) AS cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'CAG'
      AND distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND ex_dividend_date >= toDate('2021-01-01')
)
SELECT formatDateTime(px.month_start, '%Y-%m') AS month,
       formatDateTimeInJodaSyntax(px.month_start, 'MMMM yyyy') AS month_label,
       round(any(px.price), 2) AS price_usd,
       round(argMax(dv.cash_amount, dv.ex_dividend_date) * 4, 2) AS annual_dividend_usd,
       round(argMax(dv.cash_amount, dv.ex_dividend_date) * 4 / any(px.price) * 100, 2) AS dividend_yield_pct
FROM px, dv
WHERE dv.ex_dividend_date <= px.last_day
GROUP BY px.month_start
ORDER BY px.month_start