Pooled opening drop per dollar paid, by calendar year

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
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
)
SELECT
    toString(toYear(e.ex_date))                                AS year,
    count()                                                    AS payment_count,
    round(sum(p.prior_close - p.open_px) / sum(e.dividend), 2) AS drop_per_dollar_paid
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 year
ORDER BY year