Typical raise month by company: twelve dividend growers, raises since 2016

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 quarterly AS (
    SELECT ticker,
           ex_dividend_date,
           round(max(toFloat64(cash_amount)), 4) AS amount,
           max(declaration_date) AS declared
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('JNJ','KO','PG','PEP','MCD','WMT','CVX','XOM','MMM','CAT','HD','ADP')
      AND distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND declaration_date > toDate('2015-06-30')
      AND ex_dividend_date >= toDate('2015-07-01')
      AND ex_dividend_date <= toDate('2026-07-31')
    GROUP BY ticker, ex_dividend_date
),
stepped AS (
    SELECT ticker,
           declared,
           amount,
           ex_dividend_date,
           lagInFrame(amount, 1) OVER (PARTITION BY ticker ORDER BY ex_dividend_date ASC
                                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_amount,
           lagInFrame(ex_dividend_date, 1) OVER (PARTITION BY ticker ORDER BY ex_dividend_date ASC
                                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_ex_date
    FROM quarterly
),
raises AS (
    SELECT ticker,
           formatDateTimeInJodaSyntax(declared, 'MMM') AS month_label,
           toMonth(declared) AS month_index
    FROM stepped
    WHERE prior_amount > 0
      AND amount > prior_amount * 1.005
      AND dateDiff('day', prior_ex_date, ex_dividend_date) BETWEEN 45 AND 200
),
per_month AS (
    SELECT ticker,
           month_label,
           month_index,
           count() AS raises_in_month
    FROM raises
    GROUP BY ticker, month_label, month_index
)
SELECT ticker,
       argMax(month_label, raises_in_month * 100 + month_index) AS typical_month,
       sum(raises_in_month) AS raises_total,
       max(raises_in_month) AS raises_in_typical_month,
       round(100 * max(raises_in_month) / sum(raises_in_month)) AS same_month_pct
FROM per_month
GROUP BY ticker
HAVING sum(raises_in_month) >= 5
ORDER BY same_month_pct DESC, raises_total DESC