2,173 answered market questions

WITH tk AS (SELECT ['KO','JNJ','PG','XOM','CVX','VZ','MRK','PEP','MCD','MMM'] AS t),
r AS (
    SELECT ticker,
           date,
           toFloat64(close) AS close,
           lagInFrame(toFloat64(close)) OVER (PARTITION BY ticker ORDER BY date
                                              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN (SELECT arrayJoin(t) FROM tk)
      AND date BETWEEN toDate('2023-07-01') AND toDate('2026-06-30')
      AND close > 0
),
vol AS (
    SELECT ticker,
           round(100 * avg(abs(close / prev_close - 1)), 2) AS avg_daily_move_pct,
           round(quantileDeterministic(0.95)(100 * abs(close / prev_close - 1), cityHash64(date)), 2) AS p95_daily_move_pct,
           avg(close) AS avg_close
    FROM r
    WHERE prev_close > 0
    GROUP BY ticker
),
dv AS (
    SELECT ticker, avg(cash_amount) AS avg_dividend_usd
    FROM global_markets.stocks_dividends
    WHERE ticker IN (SELECT arrayJoin(t) FROM tk)
      AND distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date BETWEEN toDate('2023-07-01') AND toDate('2026-06-30')
    GROUP BY ticker
)
SELECT vol.ticker AS ticker,
       round(100 * dv.avg_dividend_usd / vol.avg_close, 2) AS dividend_pct_of_price,
       vol.avg_daily_move_pct AS avg_daily_move_pct,
       vol.p95_daily_move_pct AS p95_daily_move_pct
FROM vol
INNER JOIN dv ON vol.ticker = dv.ticker
ORDER BY dividend_pct_of_price DESC