How far ahead dividends were declared, trailing 12 months

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 declared AS
(
    SELECT
        ticker,
        ex_dividend_date,
        max(declaration_date) AS declared_on
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= today() - 365
      AND ex_dividend_date <  today()
      AND currency = 'USD'
      AND declaration_date > toDate('2000-01-01')
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, ex_dividend_date
),
lead_times AS
(
    SELECT dateDiff('day', declared_on, ex_dividend_date) AS lead_days
    FROM declared
    WHERE declared_on <= ex_dividend_date
)
SELECT
    multiIf(
        lead_days <=  7, '7 days or less',
        lead_days <= 14, '8 to 14 days',
        lead_days <= 30, '15 to 30 days',
        lead_days <= 60, '31 to 60 days',
                         'more than 60 days') AS lead_time_bucket,
    count()                                   AS dividend_count
FROM lead_times
GROUP BY lead_time_bucket
ORDER BY min(lead_days)