2,236 answered market questions

WITH tk AS (SELECT ['KO','JNJ','PG','XOM','CVX','VZ','MRK','PEP','MCD','MMM'] AS t),
px AS (
    SELECT ticker,
           date,
           toFloat64(open) AS day_open,
           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-06-01') AND toDate('2026-06-30')
      AND open > 0 AND close > 0
),
dv AS (
    SELECT ticker, ex_dividend_date AS d, max(cash_amount) AS div_amount
    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, d
)
SELECT px.ticker AS ticker,
       count() AS ex_dates,
       round(avg(dv.div_amount), 3) AS avg_dividend_usd,
       round(avg(px.prev_close - px.day_open), 3) AS avg_overnight_decline_usd,
       round(avg(px.prev_close - px.day_open) / avg(dv.div_amount), 2) AS decline_per_dividend_ratio
FROM px
INNER JOIN dv ON px.ticker = dv.ticker AND px.date = dv.d
WHERE px.prev_close > 0
GROUP BY px.ticker
ORDER BY decline_per_dividend_ratio DESC