Dividend yield by sector: median payer, equal-weighted mean, cap-weighted

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    basket AS
    (
        SELECT
            pair.1 AS ticker,
            pair.2 AS sector
        FROM
        (
            SELECT arrayJoin([
                ('XOM', 'Energy'), ('CVX', 'Energy'), ('COP', 'Energy'), ('SLB', 'Energy'),
                ('JPM', 'Financials'), ('BAC', 'Financials'), ('GS', 'Financials'), ('BLK', 'Financials'), ('AXP', 'Financials'),
                ('JNJ', 'Health Care'), ('ABBV', 'Health Care'), ('MRK', 'Health Care'), ('AMGN', 'Health Care'), ('GILD', 'Health Care'),
                ('KO', 'Staples'), ('PG', 'Staples'), ('PEP', 'Staples'), ('COST', 'Staples'), ('CL', 'Staples'),
                ('DUK', 'Utilities'), ('SO', 'Utilities'), ('AEP', 'Utilities'), ('NEE', 'Utilities'), ('XEL', 'Utilities'),
                ('AAPL', 'Tech'), ('MSFT', 'Tech'), ('AVGO', 'Tech'), ('CSCO', 'Tech'), ('ADBE', 'Tech'),
                ('CAT', 'Industrials'), ('HON', 'Industrials'), ('UNP', 'Industrials'), ('GE', 'Industrials'), ('LMT', 'Industrials'),
                ('HD', 'Discretionary'), ('MCD', 'Discretionary'), ('AMZN', 'Discretionary'), ('TSLA', 'Discretionary'), ('SBUX', 'Discretionary'),
                ('LIN', 'Materials'), ('SHW', 'Materials'), ('NEM', 'Materials'), ('DOW', 'Materials'),
                ('VZ', 'Communication'), ('T', 'Communication'), ('CMCSA', 'Communication'), ('GOOGL', 'Communication'), ('NFLX', 'Communication'),
                ('AMT', 'Real Estate'), ('PLD', 'Real Estate'), ('PSA', 'Real Estate'), ('SPG', 'Real Estate')
            ]) AS pair
        )
    ),
    price AS
    (
        SELECT
            ticker,
            max(date)                        AS price_date,
            argMax(toFloat64(close), date)   AS last_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN (SELECT ticker FROM basket)
          AND date >= today() - 20
        GROUP BY ticker
    ),
    indicated AS
    (
        SELECT
            ticker,
            toFloat64(argMax(cash_amount, ex_dividend_date))
                * toFloat64(argMax(frequency, ex_dividend_date)) AS annual_rate
        FROM global_markets.stocks_dividends
        WHERE ticker IN (SELECT ticker FROM basket)
          AND ex_dividend_date >= today() - 400
          AND frequency > 0
          AND cash_amount > 0
        GROUP BY ticker
    ),
    caps AS
    (
        SELECT
            ticker,
            argMax(toFloat64(market_cap), date) AS cap_usd
        FROM global_markets.stocks_ratios
        WHERE ticker IN (SELECT ticker FROM basket)
          AND date >= today() - 45
          AND market_cap > 0
        GROUP BY ticker
    ),
    name_level AS
    (
        SELECT
            b.sector                                                     AS sector,
            b.ticker                                                     AS ticker,
            p.price_date                                                 AS price_date,
            c.cap_usd                                                    AS market_cap,
            round(100 * ifNull(i.annual_rate, 0) / p.last_close, 2)      AS yield_pct
        FROM basket AS b
        INNER JOIN price AS p ON p.ticker = b.ticker
        INNER JOIN caps  AS c ON c.ticker = b.ticker
        LEFT  JOIN indicated AS i ON i.ticker = b.ticker
    )
SELECT
    sector,
    round(quantileDeterministicIf(0.5)(yield_pct, cityHash64(ticker), yield_pct > 0), 2) AS median_payer_pct,
    round(avg(yield_pct), 2)                                                             AS mean_all_names_pct,
    round(sum(yield_pct * market_cap) / sum(market_cap), 2)                              AS cap_weighted_pct,
    formatDateTime(max(price_date), '%b %e, %Y')                                         AS priced_through
FROM name_level
GROUP BY sector
HAVING countIf(yield_pct > 0) > 0
ORDER BY median_payer_pct DESC