XOM Dividend Increase History: Every Raise

WITH quarterly AS
(
    SELECT
        ex_dividend_date            AS ex_date,
        toFloat64(max(cash_amount)) AS rate,
        max(declaration_date)       AS declared
    FROM global_markets.stocks_dividends
    WHERE ticker = 'XOM'
      AND frequency = 4
      AND ex_dividend_date >= '2005-01-01'
      AND ex_dividend_date <= today()
    GROUP BY ex_dividend_date
),
stepped AS
(
    SELECT
        ex_date,
        declared,
        rate,
        lagInFrame(rate) OVER (ORDER BY ex_date ASC
            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_rate
    FROM quarterly
)
SELECT
    toString(ex_date)                                         AS ex_dividend_date,
    formatDateTime(ex_date, '%b %Y')                          AS ex_month_label,
    if(toYear(declared) > 1990,
       formatDateTime(declared, '%b %e, %Y'), 'not recorded')  AS declared_label,
    round(prev_rate, 4)                                       AS old_quarterly_usd,
    round(rate, 4)                                            AS new_quarterly_usd,
    round((rate / prev_rate - 1) * 100, 2)                    AS pct_increase
FROM stepped
WHERE ex_date >= '2006-01-01'
  AND prev_rate > 0
  AND rate > prev_rate
ORDER BY ex_date ASC

WITH quarterly AS
(
    SELECT
        ex_dividend_date            AS ex_date,
        toFloat64(max(cash_amount)) AS rate,
        max(declaration_date)       AS declared
    FROM global_markets.stocks_dividends
    WHERE ticker = 'XOM'
      AND frequency = 4
      AND ex_dividend_date >= '2005-01-01'
      AND ex_dividend_date <= today()
    GROUP BY ex_dividend_date
),
stepped AS
(
    SELECT
        ex_date,
        declared,
        rate,
        lagInFrame(rate) OVER (ORDER BY ex_date ASC
            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_rate
    FROM quarterly
),
raises AS
(
    SELECT declared
    FROM stepped
    WHERE ex_date >= '2006-01-01'
      AND prev_rate > 0
      AND rate > prev_rate
      AND toYear(declared) > 1990
)
SELECT
    multiIf(toQuarter(declared) = 1, 'Q1 (Jan to Mar)',
            toQuarter(declared) = 2, 'Q2 (Apr to Jun)',
            toQuarter(declared) = 3, 'Q3 (Jul to Sep)',
                                     'Q4 (Oct to Dec)') AS declaration_quarter,
    count()                                             AS raises_declared,
    toString(min(toYear(declared)))                     AS first_year,
    toString(max(toYear(declared)))                     AS latest_year
FROM raises
GROUP BY declaration_quarter
ORDER BY raises_declared DESC, latest_year DESC

WITH quarterly AS
(
    SELECT
        ex_dividend_date            AS ex_date,
        toFloat64(max(cash_amount)) AS rate
    FROM global_markets.stocks_dividends
    WHERE ticker = 'XOM'
      AND frequency = 4
      AND ex_dividend_date >= '2005-01-01'
      AND ex_dividend_date <= today()
    GROUP BY ex_dividend_date
),
stepped AS
(
    SELECT
        ex_date,
        rate,
        lagInFrame(rate) OVER (ORDER BY ex_date ASC
            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_rate
    FROM quarterly
)
SELECT
    toYear(ex_date)                                     AS year,
    round(sum(rate), 4)                                 AS paid_per_share_usd,
    count()                                             AS payments,
    countIf(prev_rate > 0 AND rate > prev_rate)         AS raises_declared
FROM stepped
WHERE toYear(ex_date) >= 2006
  AND toYear(ex_date) < toYear(today())
GROUP BY year
ORDER BY year ASC

WITH quarterly AS
(
    SELECT
        ex_dividend_date            AS ex_date,
        toFloat64(max(cash_amount)) AS rate
    FROM global_markets.stocks_dividends
    WHERE ticker = 'XOM'
      AND frequency = 4
      AND ex_dividend_date >= '2005-01-01'
      AND ex_dividend_date <= today()
    GROUP BY ex_dividend_date
),
stepped AS
(
    SELECT
        ex_date,
        rate,
        lagInFrame(rate) OVER (ORDER BY ex_date ASC
            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_rate
    FROM quarterly
),
yearly AS
(
    SELECT
        toYear(ex_date)                             AS y,
        sum(rate)                                   AS total,
        countIf(prev_rate > 0 AND rate > prev_rate)  AS raises
    FROM stepped
    WHERE toYear(ex_date) >= 2006
      AND toYear(ex_date) < toYear(today())
    GROUP BY y
),
framed AS
(
    SELECT
        y,
        total,
        raises,
        lagInFrame(total) OVER (ORDER BY y ASC
            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_total
    FROM yearly
),
marks AS
(
    SELECT
        max(y)                                                             AS last_year,
        greatest(maxIf(y, prev_total > 0 AND total <= prev_total), min(y))  AS last_total_break,
        greatest(maxIf(y, raises = 0), min(y))                             AS last_rate_break,
        countIf(raises = 0)                                                AS flat_years,
        maxIf(y, raises = 0)                                               AS latest_flat_year
    FROM framed
)
SELECT
    tupleElement(t, 1)              AS basis,
    toUInt32(tupleElement(t, 2))    AS years_counted,
    tupleElement(t, 3)              AS starting_year
FROM marks
ARRAY JOIN
[
    (toString('Annual total per share above the prior year'),
     toInt32(last_year - last_total_break),
     concat('run from ', toString(last_total_break + 1))),
    (toString('Calendar years with an increase declared'),
     toInt32(last_year - last_rate_break),
     concat('run from ', toString(last_rate_break + 1))),
    (toString('Calendar years with no increase declared'),
     toInt32(flat_years),
     if(flat_years = 0, 'none in window', concat('latest ', toString(latest_flat_year))))
] AS t

WITH quarterly AS
(
    SELECT
        ex_dividend_date            AS ex_date,
        toFloat64(max(cash_amount)) AS rate
    FROM global_markets.stocks_dividends
    WHERE ticker = 'XOM'
      AND frequency = 4
      AND ex_dividend_date >= '2000-01-01'
      AND ex_dividend_date <= today()
    GROUP BY ex_dividend_date
),
series AS
(
    SELECT
        groupArray(ex_date) AS ds,
        groupArray(rate)    AS rs
    FROM
    (
        SELECT ex_date, rate
        FROM quarterly
        ORDER BY ex_date ASC
    )
),
spans AS
(
    SELECT
        ds,
        rs,
        yrs,
        rs[length(rs)] AS end_rate,
        greatest(arrayFirstIndex(d -> d >= addYears(ds[length(ds)], -1 * toInt32(yrs)), ds), 1) AS idx
    FROM series
    ARRAY JOIN [5, 10, 20] AS yrs
)
SELECT
    concat(toString(yrs), '-year span')                            AS horizon,
    formatDateTime(ds[idx], '%b %Y')                               AS start_ex_label,
    round(rs[idx], 4)                                              AS start_quarterly_usd,
    round(end_rate, 4)                                             AS latest_quarterly_usd,
    round((pow(end_rate / rs[idx], 1.0 / yrs) - 1) * 100, 2)       AS cagr_pct,
    round(end_rate / rs[idx], 2)                                   AS growth_multiple
FROM spans
ORDER BY yrs ASC