2,170 answered market questions

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 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, declared
    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
),
chained AS (
    SELECT ticker,
           declared,
           lagInFrame(declared, 1) OVER (PARTITION BY ticker ORDER BY declared ASC
                                         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_declared
    FROM raises
)
SELECT toYear(declared) AS year,
       uniqExact(ticker) AS companies,
       round(100 * countIf(toMonth(declared) = toMonth(prior_declared)) / count(), 1) AS same_month_pct,
       round(100 * countIf(abs(toInt32(toRelativeMonthNum(declared))
                               - toInt32(toRelativeMonthNum(prior_declared)) - 12) <= 1) / count(), 1) AS within_one_month_pct
FROM chained
WHERE declared >= toDate('2017-01-01')
  AND dateDiff('day', prior_declared, declared) BETWEEN 250 AND 480
GROUP BY year
ORDER BY year