The universe choice: sector means with and without non-payers

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,
            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
    ),
    name_level AS
    (
        SELECT
            b.sector                                                AS sector,
            b.ticker                                                AS ticker,
            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
        LEFT  JOIN indicated AS i ON i.ticker = b.ticker
    )
SELECT
    sector,
    countIf(yield_pct > 0)                                              AS dividend_payers,
    countIf(yield_pct = 0)                                              AS non_payers,
    round(avgIf(yield_pct, yield_pct > 0), 2)                           AS payers_only_mean_pct,
    round(avg(yield_pct), 2)                                            AS all_names_mean_pct,
    round(avgIf(yield_pct, yield_pct > 0) - avg(yield_pct), 2)          AS gap_pct
FROM name_level
GROUP BY sector
HAVING countIf(yield_pct > 0) > 0
ORDER BY gap_pct DESC