Consecutive years of dividend increases: six long record US payers

WITH yearly AS (
    SELECT ticker,
           toYear(ex_dividend_date) AS year,
           max(cash_amount) AS top_amount
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('VZ', 'KO', 'JNJ', 'PG', 'MCD', 'MMM')
      AND distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date >= toDate('2001-01-01')
      AND ex_dividend_date < toDate('2026-01-01')
    GROUP BY ticker, year
),
prior AS (
    SELECT ticker,
           year + 1 AS year,
           top_amount AS prior_top
    FROM yearly
),
flagged AS (
    SELECT yearly.ticker AS ticker,
           year,
           yearly.top_amount > prior.prior_top AS raised
    FROM yearly
    INNER JOIN prior USING (ticker, year)
)
SELECT ticker,
       max(year) - greatest(maxIf(year, raised = 0), min(year) - 1) AS consecutive_increase_years,
       toString(min(year)) AS first_year_compared,
       toString(max(year)) AS last_year_compared
FROM flagged
GROUP BY ticker
ORDER BY consecutive_increase_years ASC, ticker ASC