Ex-date opening gap by dividend size, US quarterly dividends 2024 to 2025

WITH px AS
(
    SELECT
        ticker,
        date,
        toFloat64(open) AS open_px,
        volume,
        toFloat64(any(close) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) AS prior_close
    FROM global_markets.stocks_daily_aggs
    WHERE date BETWEEN toDate('2023-12-15') AND toDate('2025-12-31')
),
ex AS
(
    SELECT
        ticker,
        ex_dividend_date,
        max(cash_amount) AS div_cash
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date BETWEEN toDate('2024-01-01') AND toDate('2025-12-31')
      AND frequency = 4
      AND cash_amount > 0
      AND currency = 'USD'
    GROUP BY ticker, ex_dividend_date
)
SELECT
    multiIf(
        div_pct < 0.25, 'under 0.25%',
        div_pct < 0.50, '0.25% to 0.50%',
        div_pct < 1.00, '0.50% to 1.00%',
        div_pct < 2.00, '1.00% to 2.00%',
                        'over 2.00%')  AS dividend_bucket,
    count()                            AS sample_size,
    round(avg(div_pct), 3)             AS avg_dividend_pct,
    round(avg(open_gap_pct), 3)        AS avg_open_gap_pct
FROM
(
    SELECT
        100 * toFloat64(ex.div_cash) / px.prior_close         AS div_pct,
        100 * (px.prior_close - px.open_px) / px.prior_close AS open_gap_pct
    FROM px
    INNER JOIN ex ON px.ticker = ex.ticker AND px.date = ex.ex_dividend_date
    WHERE px.prior_close >= 10
      AND px.open_px > 0
      AND px.volume >= 1000000
)
GROUP BY dividend_bucket
ORDER BY avg_dividend_pct