Dividends paid as a share of operating cash flow, by sector

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
        )
    ),
    quarters AS
    (
        SELECT
            ticker,
            period_end,
            max(abs(toFloat64(dividends)))                          AS dividends_paid,
            max(toFloat64(net_cash_from_operating_activities))      AS operating_cash_flow
        FROM
        (
            SELECT
                arrayJoin(tickers) AS ticker,
                period_end,
                dividends,
                net_cash_from_operating_activities
            FROM global_markets.stocks_cash_flow_statements
            WHERE timeframe = 'quarterly'
              AND period_end >= today() - 500
              AND period_end <= today()
        )
        GROUP BY ticker, period_end
    ),
    joined AS
    (
        SELECT
            b.sector               AS sector,
            q.ticker               AS ticker,
            q.dividends_paid       AS dividends_paid,
            q.operating_cash_flow  AS operating_cash_flow
        FROM quarters AS q
        INNER JOIN basket AS b ON b.ticker = q.ticker
        WHERE q.operating_cash_flow > 0
    )
SELECT
    sector,
    countDistinct(ticker)                                          AS names_with_filings,
    round(100 * sum(dividends_paid) / sum(operating_cash_flow), 1) AS dividends_pct_of_cash_flow
FROM joined
GROUP BY sector
HAVING sum(operating_cash_flow) > 0
ORDER BY dividends_pct_of_cash_flow DESC