Ex-dividend openings sorted by decline as a multiple of the dividend: US quarterly payers, Jan 2024 to Jun 2026

WITH divs AS (
    SELECT ticker, ex_dividend_date AS d, max(cash_amount) AS div_amount
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND ex_dividend_date BETWEEN toDate('2024-01-01') AND toDate('2026-06-30')
    GROUP BY ticker, d
),
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 date BETWEEN toDate('2023-12-01') AND toDate('2026-06-30')
      AND open > 0 AND close > 0
),
ev AS (
    SELECT (px.prev_close - px.day_open) / divs.div_amount AS ratio
    FROM px
    INNER JOIN divs ON px.ticker = divs.ticker AND px.date = divs.d
    WHERE px.prev_close >= 5
      AND divs.div_amount / px.prev_close BETWEEN 0.001 AND 0.05
)
SELECT multiIf(ratio < 0, 'opened higher',
               ratio < 0.5, 'fell under half',
               ratio < 1, 'fell half to full',
               ratio < 1.5, 'fell 1 to 1.5x',
               'fell over 1.5x') AS drop_bucket,
       count() AS ex_dates,
       round(100 * count() / (SELECT count() FROM ev), 1) AS share_pct
FROM ev
GROUP BY drop_bucket
ORDER BY multiIf(drop_bucket = 'opened higher', 0,
                 drop_bucket = 'fell under half', 1,
                 drop_bucket = 'fell half to full', 2,
                 drop_bucket = 'fell 1 to 1.5x', 3, 4)