3,256 answered market questions

WITH snapshot AS (
    SELECT ticker,
           toFloat64(dividend_yield) * 100 AS yield_pct,
           toFloat64(price) AS price_usd,
           toFloat64(dividend_yield) * toFloat64(price) AS annual_dividend,
           toFloat64(earnings_per_share) AS eps
    FROM global_markets.stocks_ratios
    WHERE date = (SELECT max(date) FROM global_markets.stocks_ratios)
      AND price >= 5
      AND market_cap >= 2000000000
      AND dividend_yield > 0
      AND earnings_per_share > 0
),
payments AS (
    SELECT ticker,
           sumIf(toFloat64(cash_amount), ex_dividend_date > today() - INTERVAL 1 YEAR) AS ttm_paid,
           sumIf(toFloat64(cash_amount), ex_dividend_date <= today() - INTERVAL 1 YEAR
                 AND ex_dividend_date > today() - INTERVAL 2 YEAR) AS prior_paid
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date > today() - INTERVAL 2 YEAR
      AND ex_dividend_date <= today()
    GROUP BY ticker
    HAVING ttm_paid > 0 AND prior_paid > 0
)
SELECT s.ticker AS ticker,
       round(s.yield_pct, 2) AS dividend_yield_pct,
       round(s.annual_dividend / s.eps * 100, 0) AS payout_ratio,
       round(100 * (p.ttm_paid - p.prior_paid) / p.prior_paid, 1) AS dividend_change_1y_pct,
       round(s.price_usd, 2) AS share_price
FROM snapshot AS s
INNER JOIN payments AS p ON s.ticker = p.ticker
ORDER BY dividend_yield_pct DESC
LIMIT 15