S&P 500 배당수익률 계산법

SELECT multiIf(market_cap >= 1000000000000, 'over $1T',
               market_cap >= 200000000000, '$200B to $1T',
               market_cap >= 50000000000, '$50B to $200B',
               market_cap >= 10000000000, '$10B to $50B',
               '$1B to $10B') AS size_bucket,
       count() AS companies,
       countIf(dividend_yield > 0) AS payers,
       round(100 * sum(ifNull(dividend_yield, 0) * market_cap) / sum(market_cap), 2) AS cap_weighted_yield_pct,
       round(100 * avg(ifNull(dividend_yield, 0)), 2) AS equal_weighted_yield_pct,
       round(100 * quantileDeterministicIf(0.5)(ifNull(dividend_yield, 0),
                                                cityHash64(ticker), dividend_yield > 0), 2) AS median_payer_yield_pct
FROM global_markets.stocks_ratios
WHERE date = (SELECT max(date) FROM global_markets.stocks_ratios)
  AND price >= 5
  AND market_cap >= 1000000000
GROUP BY size_bucket
HAVING countIf(dividend_yield > 0) > 0
ORDER BY max(market_cap) DESC

WITH px AS (
    SELECT ticker,
           argMax(close, window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'RSP', 'VYM', 'SCHD', 'SPYD', 'XLU', 'XLP', 'XLRE', 'XLK')
      AND window_start >= now() - INTERVAL 7 DAY
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker
),
dv AS (
    SELECT ticker,
           sum(cash_amount) AS ttm_distributions,
           count() AS payments
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('SPY', 'RSP', 'VYM', 'SCHD', 'SPYD', 'XLU', 'XLP', 'XLRE', 'XLK')
      AND ex_dividend_date > today() - INTERVAL 1 YEAR
      AND ex_dividend_date <= today()
      AND cash_amount > 0
    GROUP BY ticker
)
SELECT px.ticker AS ticker,
       multiIf(px.ticker = 'SPY', 'broad index tracker',
               px.ticker = 'RSP', 'equal weight S&P 500',
               px.ticker IN ('VYM', 'SCHD', 'SPYD'), 'dividend screen',
               'sector fund') AS segment,
       round(px.price, 2) AS price,
       round(dv.ttm_distributions, 3) AS ttm_distributions_usd,
       dv.payments AS payments,
       round(dv.ttm_distributions / px.price * 100, 2) AS trailing_yield_pct
FROM px
INNER JOIN dv ON px.ticker = dv.ticker
ORDER BY multiIf(px.ticker = 'SPY', 1,
                 px.ticker = 'RSP', 2,
                 px.ticker = 'VYM', 3,
                 px.ticker = 'SCHD', 4,
                 px.ticker = 'SPYD', 5,
                 px.ticker = 'XLU', 6,
                 px.ticker = 'XLP', 7,
                 px.ticker = 'XLRE', 8,
                 9)

WITH px AS (
    SELECT toYear(toTimeZone(window_start, 'America/New_York')) AS year,
           argMax(close, window_start) AS year_end_price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toYear(toTimeZone(window_start, 'America/New_York')) BETWEEN 2015 AND 2025
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY year
),
dv AS (
    SELECT toYear(ex_dividend_date) AS year,
           sum(cash_amount) AS annual_distributions,
           count() AS payments
    FROM global_markets.stocks_dividends
    WHERE ticker = 'SPY'
      AND cash_amount > 0
      AND ex_dividend_date >= toDate('2015-01-01')
      AND ex_dividend_date <= toDate('2025-12-31')
    GROUP BY year
),
tsy AS (
    SELECT toYear(date) AS year,
           argMax(yield_10_year, date) AS y10
    FROM global_markets.treasury_yields
    WHERE toYear(date) BETWEEN 2015 AND 2025
      AND toMonth(date) = 12
      AND yield_10_year IS NOT NULL
    GROUP BY year
)
SELECT px.year AS year,
       round(dv.annual_distributions / px.year_end_price * 100, 2) AS tracker_yield_pct,
       round(tsy.y10, 2) AS treasury_10y_pct,
       round(dv.annual_distributions, 2) AS distributions_per_share_usd,
       dv.payments AS payments
FROM px
INNER JOIN dv ON px.year = dv.year
INNER JOIN tsy ON px.year = tsy.year
ORDER BY year

WITH px AS (
    SELECT toYear(toTimeZone(window_start, 'America/New_York')) AS year,
           argMax(close, window_start) AS year_end_price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toYear(toTimeZone(window_start, 'America/New_York')) BETWEEN 2015 AND 2025
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY year
),
dv AS (
    SELECT toYear(ex_dividend_date) AS year,
           sum(cash_amount) AS annual_distributions
    FROM global_markets.stocks_dividends
    WHERE ticker = 'SPY'
      AND cash_amount > 0
      AND ex_dividend_date >= toDate('2015-01-01')
      AND ex_dividend_date <= toDate('2025-12-31')
    GROUP BY year
)
SELECT px.year AS year,
       round(100 * dv.annual_distributions / (SELECT annual_distributions FROM dv WHERE year = 2015), 1) AS distributions_index_2015_100,
       round(100 * px.year_end_price / (SELECT year_end_price FROM px WHERE year = 2015), 1) AS price_index_2015_100
FROM px
INNER JOIN dv ON px.year = dv.year
ORDER BY year