streak

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
        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