SPY 배당수익률이 S&P 500보다 낮은 이유

WITH
    divs AS
    (
        SELECT
            ticker,
            ex_dividend_date,
            max(toFloat64(cash_amount)) AS cash_amount
        FROM global_markets.stocks_dividends
        WHERE ticker IN ('SPY', 'VOO', 'IVV')
          AND ex_dividend_date >= today() - 365
          AND ex_dividend_date <  today()
        GROUP BY ticker, ex_dividend_date
    ),
    ttm AS
    (
        SELECT
            ticker,
            sum(cash_amount) AS ttm_dividend
        FROM divs
        GROUP BY ticker
        HAVING count() = 4
    ),
    px AS
    (
        SELECT
            ticker,
            toFloat64(argMax(close, window_start)) AS last_close
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('SPY', 'VOO', 'IVV')
          AND window_start >= today() - 15
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) <  960
        GROUP BY ticker
    ),
    fund_yield AS
    (
        SELECT
            ttm.ticker                             AS ticker,
            ttm.ttm_dividend / px.last_close * 100 AS yield_pct
        FROM ttm
        INNER JOIN px ON px.ticker = ttm.ticker
    ),
    spy_yield AS
    (
        SELECT max(yield_pct) AS spy_pct
        FROM fund_yield
        WHERE ticker = 'SPY'
    )
SELECT
    fund_yield.ticker                                          AS ticker,
    round(fund_yield.yield_pct, 3)                             AS trailing_yield_pct,
    round((fund_yield.yield_pct - spy_yield.spy_pct) * 100, 1) AS yield_spread_vs_spy_bps
FROM fund_yield
CROSS JOIN spy_yield
ORDER BY ticker

WITH divs AS
(
    SELECT
        ticker,
        ex_dividend_date,
        any(pay_date) AS payment_date
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('SPY', 'VOO', 'IVV')
      AND ex_dividend_date >= today() - 1095
      AND ex_dividend_date <  today()
      AND pay_date > ex_dividend_date
    GROUP BY ticker, ex_dividend_date
)
SELECT
    ticker,
    round(avg(dateDiff('day', ex_dividend_date, payment_date)), 1) AS avg_days_cash_held,
    max(dateDiff('day', ex_dividend_date, payment_date))           AS longest_wait_days,
    count()                                                        AS payout_count
FROM divs
GROUP BY ticker
ORDER BY ticker

WITH
    divs AS
    (
        SELECT
            ex_dividend_date            AS ex_date,
            any(pay_date)               AS pay_date,
            max(toFloat64(cash_amount)) AS cash_amount
        FROM global_markets.stocks_dividends
        WHERE ticker = 'SPY'
          AND ex_dividend_date >= today() - 1095
          AND ex_dividend_date <  today()
        GROUP BY ex_dividend_date
    ),
    px AS
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            toFloat64(argMax(close, window_start))               AS close_px
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 1105
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) <  960
        GROUP BY d
    )
SELECT
    toString(divs.ex_date)                         AS ex_date,
    formatDateTime(divs.ex_date, '%b %e, %Y')      AS ex_date_label,
    formatDateTime(divs.pay_date, '%b %e, %Y')     AS pay_date_label,
    round(divs.cash_amount, 4)                     AS cash_amount,
    round(divs.cash_amount / px.close_px * 100, 3) AS pct_of_price
FROM divs
INNER JOIN px ON px.d = divs.ex_date
ORDER BY ex_date

WITH
    px AS
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            toFloat64(argMax(close, window_start))               AS close_px
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 2950
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) <  960
        GROUP BY d
    ),
    by_year AS
    (
        SELECT
            toYear(d)           AS year,
            argMin(close_px, d) AS first_close,
            argMax(close_px, d) AS last_close
        FROM px
        GROUP BY year
    ),
    divs AS
    (
        SELECT
            toYear(ex_date)  AS year,
            sum(cash_amount) AS year_dividends
        FROM
        (
            SELECT
                ex_dividend_date            AS ex_date,
                max(toFloat64(cash_amount)) AS cash_amount
            FROM global_markets.stocks_dividends
            WHERE ticker = 'SPY'
              AND ex_dividend_date >= today() - 2950
            GROUP BY ex_dividend_date
        )
        GROUP BY year
    )
SELECT
    by_year.year                                                   AS year,
    round((by_year.last_close / by_year.first_close - 1) * 100, 2) AS price_return_pct,
    round(divs.year_dividends / by_year.first_close * 100, 2)      AS dividend_return_pct
FROM by_year
INNER JOIN divs ON divs.year = by_year.year
WHERE by_year.year > toYear(today() - 2950)
  AND by_year.year < toYear(today())
ORDER BY year