Quarterly dividend against ordinary overnight moves, 2021 to 2025

WITH
prices AS
(
    SELECT
        ticker,
        date,
        open_px,
        any(close_px) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM
    (
        SELECT
            ticker,
            date,
            toFloat64(max(open))  AS open_px,
            toFloat64(max(close)) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('KO','PG','JNJ','PEP','XOM','CVX','MCD','HD','VZ','ABBV')
          AND date >= '2020-12-01'
          AND date <  '2026-01-01'
        GROUP BY ticker, date
    )
),
ex_days AS
(
    SELECT
        ticker,
        ex_dividend_date            AS ex_date,
        toFloat64(max(cash_amount)) AS dividend
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO','PG','JNJ','PEP','XOM','CVX','MCD','HD','VZ','ABBV')
      AND ex_dividend_date >= '2021-01-01'
      AND ex_dividend_date <  '2026-01-01'
      AND cash_amount > 0
    GROUP BY ticker, ex_dividend_date
),
div_size AS
(
    SELECT
        e.ticker                                AS ticker,
        avg(e.dividend / p.prior_close) * 10000 AS dividend_bps
    FROM ex_days AS e
    INNER JOIN prices AS p ON p.ticker = e.ticker AND p.date = e.ex_date
    WHERE p.prior_close > 0
    GROUP BY e.ticker
),
ordinary AS
(
    SELECT
        ticker,
        date,
        abs(open_px / prior_close - 1) * 10000 AS abs_gap_bps
    FROM prices
    WHERE prior_close > 0
      AND date >= '2021-01-01'
      AND (ticker, date) NOT IN (SELECT ticker, ex_date FROM ex_days)
)
SELECT
    o.ticker                                                              AS ticker,
    round(max(d.dividend_bps), 1)                                         AS dividend_bps,
    round(quantileDeterministic(0.5)(o.abs_gap_bps, toUInt32(o.date)), 1) AS median_gap_bps,
    round(100 * countIf(o.abs_gap_bps > d.dividend_bps) / count(), 1)     AS sessions_over_dividend_pct
FROM ordinary AS o
INNER JOIN div_size AS d ON d.ticker = o.ticker
GROUP BY o.ticker
ORDER BY sessions_over_dividend_pct DESC