The market's dividend yield vs. the 10-year Treasury: end-June readings, 2016-2026

WITH spy_px AS (
    SELECT toYear(toTimeZone(window_start, 'America/New_York')) AS year,
           argMax(close, window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toMonth(toTimeZone(window_start, 'America/New_York')) = 6
      AND toDayOfMonth(toTimeZone(window_start, 'America/New_York')) >= 25
      AND toYear(toTimeZone(window_start, 'America/New_York')) >= 2016
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY year
),
spy_div AS (
    SELECT toYear(addMonths(ex_dividend_date, 6)) AS year,
           sum(cash_amount) AS ttm_div
    FROM global_markets.stocks_dividends
    WHERE ticker = 'SPY' AND cash_amount > 0 AND ex_dividend_date >= toDate('2015-07-01')
    GROUP BY year
),
tsy AS (
    SELECT toYear(date) AS year,
           argMax(yield_10_year, date) AS y10
    FROM global_markets.treasury_yields
    WHERE toMonth(date) = 6 AND toYear(date) >= 2016 AND yield_10_year IS NOT NULL
    GROUP BY year
)
SELECT spy_px.year AS year,
       round(spy_div.ttm_div / spy_px.price * 100, 2) AS market_dividend_yield_pct,
       round(tsy.y10, 2) AS treasury_10y_pct,
       round(tsy.y10 - spy_div.ttm_div / spy_px.price * 100, 2) AS treasury_minus_stocks_pct,
       round(abs(tsy.y10 - spy_div.ttm_div / spy_px.price * 100), 2) AS gap_abs_pct
FROM spy_px
INNER JOIN spy_div ON spy_px.year = spy_div.year
INNER JOIN tsy ON spy_px.year = tsy.year
ORDER BY year