2,173 answered market questions

SELECT
    d.ticker                                                            AS ticker,
    round(100 * d.ttm_dividend / p.price, 2)                            AS dividend_yield_pct,
    round(100 * (s.shares_prior - s.shares_recent) / s.shares_prior, 2) AS buyback_yield_pct,
    round(100 * d.ttm_dividend / p.price
        + 100 * (s.shares_prior - s.shares_recent) / s.shares_prior, 2) AS shareholder_yield_pct
FROM
(
    SELECT
        ticker,
        toFloat64(sum(amount)) AS ttm_dividend
    FROM
    (
        SELECT
            ticker,
            id,
            any(cash_amount) AS amount
        FROM global_markets.stocks_dividends
        WHERE ticker IN ('AAPL', 'MSFT', 'KO', 'PEP', 'CVX', 'CSCO', 'HD', 'JNJ')
          AND frequency > 0
          AND ex_dividend_date > today() - 365
          AND ex_dividend_date <= today()
        GROUP BY ticker, id
    )
    GROUP BY ticker
) AS d
INNER JOIN
(
    SELECT
        ticker,
        toFloat64(argMax(close, date)) AS price
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'KO', 'PEP', 'CVX', 'CSCO', 'HD', 'JNJ')
      AND date > today() - 30
    GROUP BY ticker
) AS p ON p.ticker = d.ticker
INNER JOIN
(
    SELECT
        arrayJoin(tickers) AS ticker,
        argMaxIf(toFloat64(diluted_shares_outstanding), period_end, period_end > today() - 200)  AS shares_recent,
        argMaxIf(toFloat64(diluted_shares_outstanding), period_end, period_end <= today() - 365) AS shares_prior
    FROM global_markets.stocks_income_statements
    WHERE timeframe = 'quarterly'
      AND diluted_shares_outstanding > 0
      AND period_end > today() - 800
      AND hasAny(tickers, ['AAPL', 'MSFT', 'KO', 'PEP', 'CVX', 'CSCO', 'HD', 'JNJ'])
    GROUP BY ticker
    HAVING ticker IN ('AAPL', 'MSFT', 'KO', 'PEP', 'CVX', 'CSCO', 'HD', 'JNJ')
       AND countIf(period_end > today() - 200) > 0
       AND countIf(period_end <= today() - 365) > 0
) AS s ON s.ticker = d.ticker
ORDER BY shareholder_yield_pct DESC