3,256 answered market questions

WITH
quarters AS
(
    SELECT
        arrayJoin(tickers) AS ticker,
        period_end,
        argMax(toFloat64(diluted_shares_outstanding), filing_date) AS shares
    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, period_end
    HAVING ticker IN ('AAPL', 'MSFT', 'KO', 'PEP', 'CVX', 'CSCO', 'HD', 'JNJ')
),
latest AS
(
    SELECT
        ticker,
        max(period_end)               AS recent_period,
        argMax(shares, period_end)    AS shares_recent
    FROM quarters
    GROUP BY ticker
    HAVING recent_period > today() - 400
),
share_pairs AS
(
    SELECT
        r.ticker                                                                       AS ticker,
        any(r.recent_period)                                                           AS recent_period,
        any(r.shares_recent)                                                           AS shares_recent,
        argMin(q.period_end, abs(dateDiff('day', q.period_end, r.recent_period - 365))) AS prior_period,
        argMin(q.shares, abs(dateDiff('day', q.period_end, r.recent_period - 365)))     AS shares_prior
    FROM latest AS r
    INNER JOIN quarters AS q ON q.ticker = r.ticker
    WHERE q.period_end BETWEEN r.recent_period - 400 AND r.recent_period - 330
    GROUP BY r.ticker
)
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 share_pairs AS s ON s.ticker = d.ticker
ORDER BY shareholder_yield_pct DESC