KO's ex-date opening print, measured against both references

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    px AS
    (
        SELECT
            date,
            max(open)  AS open_px,
            max(close) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'KO'
          AND date >= today() - 1300
        GROUP BY date
    ),
    daily AS
    (
        SELECT
            date,
            open_px,
            any(close_px) OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
        FROM px
    ),
    divs AS
    (
        SELECT
            ex_dividend_date AS ex_date,
            max(cash_amount) AS dividend_usd
        FROM global_markets.stocks_dividends
        WHERE ticker = 'KO'
          AND cash_amount > 0
          AND ex_dividend_date >= today() - 1200
          AND ex_dividend_date <  today()
        GROUP BY ex_dividend_date
    )
SELECT
    toString(v.ex_date) AS ex_date,
    round(100 * (toFloat64(d.open_px) - toFloat64(d.prior_close)) / toFloat64(d.prior_close), 2) AS vs_prior_close_pct,
    round(100 * (toFloat64(d.open_px) - (toFloat64(d.prior_close) - toFloat64(v.dividend_usd)))
              / (toFloat64(d.prior_close) - toFloat64(v.dividend_usd)), 2)                       AS vs_adjusted_close_pct
FROM divs AS v
INNER JOIN daily AS d ON d.date = v.ex_date
ORDER BY ex_date ASC