Gap from ex-dividend date to record date, US listed dividends by 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 deduped AS
(
    SELECT
        id,
        any(ex_dividend_date) AS ex_date,
        any(record_date)      AS rec_date
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= toDate('2019-01-01')
      AND ex_dividend_date <  today()
      AND record_date >= ex_dividend_date
      AND dateDiff('day', ex_dividend_date, record_date) <= 7
    GROUP BY id
)
SELECT
    toYear(ex_date)                                        AS year,
    round(avg(dateDiff('day', ex_date, rec_date)), 2)      AS avg_days_ex_to_record,
    round(100 * countIf(rec_date = ex_date) / count(), 1)  AS same_day_pct,
    count()                                                AS dividends_counted
FROM deduped
GROUP BY year
ORDER BY year