Coca-Cola (KO): overnight decline vs dividend on each ex-dividend date, Sep 2023 to Jun 2026

WITH px AS (
    SELECT date,
           toFloat64(open) AS day_open,
           lagInFrame(toFloat64(close)) OVER (ORDER BY date
                                              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'KO'
      AND date BETWEEN toDate('2023-06-01') AND toDate('2026-06-30')
      AND open > 0 AND close > 0
),
dv AS (
    SELECT ex_dividend_date AS d, max(cash_amount) AS div_amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'KO'
      AND distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date BETWEEN toDate('2023-07-01') AND toDate('2026-06-30')
    GROUP BY d
)
SELECT formatDateTime(px.date, '%Y-%m-%d') AS date,
       round(dv.div_amount, 3) AS dividend_usd,
       round(px.prev_close - px.day_open, 3) AS overnight_decline_usd,
       round((px.prev_close - px.day_open) - dv.div_amount, 3) AS decline_minus_dividend_usd
FROM px
INNER JOIN dv ON px.date = dv.d
ORDER BY date