raises

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.

SELECT
    year,
    raised_in,
    raise_pct,
    new_rate,
    count() OVER (PARTITION BY streak_group ORDER BY year ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS consecutive_years
FROM
(
    SELECT
        year,
        raised_in,
        raise_pct,
        new_rate,
        year - row_number() OVER (ORDER BY year ASC) AS streak_group
    FROM
    (
        SELECT
            toYear(ex_date)                                             AS year,
            argMin(formatDateTime(ex_date, '%b'), ex_date)              AS raised_in,
            round(argMin(100 * (amt - prev_amt) / prev_amt, ex_date), 2) AS raise_pct,
            round(argMin(amt, ex_date), 4)                              AS new_rate
        FROM
        (
            SELECT
                ex_date,
                amt,
                lagInFrame(amt) OVER (ORDER BY ex_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_amt
            FROM
            (
                SELECT
                    ex_dividend_date            AS ex_date,
                    max(toFloat64(cash_amount)) AS amt
                FROM global_markets.stocks_dividends
                WHERE ticker = 'PEP'
                GROUP BY ex_dividend_date
            )
        )
        WHERE prev_amt > 0 AND amt > prev_amt * 1.001
        GROUP BY year
    )
)
ORDER BY year