2,173 answered market questions

WITH tickers AS (
    SELECT arrayJoin(['KO','JNJ','PG','PEP','MCD','MMM','MO','VZ','XOM','CVX','IBM','KMB','O','T','ABBV']) AS ticker
),
divs AS (
    SELECT ticker, sum(cash_amount) AS annual_div
    FROM global_markets.stocks_dividends
    WHERE ticker IN (SELECT ticker FROM tickers)
      AND frequency IN (1, 2, 4, 12)
      AND ex_dividend_date >= today() - 370
    GROUP BY ticker
),
px AS (
    SELECT ticker, argMax(toFloat64(close), window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN (SELECT ticker FROM tickers)
      AND window_start >= now() - INTERVAL 5 DAY
    GROUP BY ticker
),
tb AS (
    SELECT round(yield_3_month, 2) AS t3mo
    FROM global_markets.treasury_yields
    ORDER BY date DESC
    LIMIT 1
),
ylds AS (
    SELECT d.ticker AS ticker,
           round(100 * d.annual_div / p.price, 2) AS dy
    FROM divs d
    INNER JOIN px p ON d.ticker = p.ticker
)
SELECT count() AS payers,
       countIf(dy < (SELECT t3mo FROM tb)) AS below_tbill,
       countIf(dy >= (SELECT t3mo FROM tb)) AS at_or_above_tbill,
       (SELECT t3mo FROM tb) AS tbill_3mo_pct
FROM ylds