2,170 answered market questions

WITH px AS (
    SELECT ticker,
           toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York'))) AS month_start,
           argMax(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 IN ('SCHD', 'VOO')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2025-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 ticker, month_start
),
dv AS (
    SELECT ticker, ex_dividend_date, cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('SCHD', 'VOO')
      AND cash_amount > 0
      AND ex_dividend_date >= toDate('2024-12-01')
),
latest AS (
    SELECT px.ticker AS ticker,
           px.month_start AS month_start,
           any(px.price) AS price,
           argMax(dv.cash_amount, dv.ex_dividend_date) AS latest_payment
    FROM px, dv
    WHERE px.ticker = dv.ticker
      AND dv.ex_dividend_date <= px.last_day
    GROUP BY px.ticker, px.month_start
)
SELECT formatDateTime(month_start, '%Y-%m') AS month,
       formatDateTimeInJodaSyntax(month_start, 'MMMM yyyy') AS month_label,
       round(sumIf(latest_payment * 4 / price * 100, ticker = 'SCHD'), 2) AS schd_yield_pct,
       round(sumIf(latest_payment * 4 / price * 100, ticker = 'VOO'), 2) AS voo_yield_pct,
       round(sumIf(latest_payment * 4 / price * 100, ticker = 'SCHD')
             - sumIf(latest_payment * 4 / price * 100, ticker = 'VOO'), 2) AS gap_pct
FROM latest
GROUP BY month_start
ORDER BY month_start