raises

WITH paid AS
(
    SELECT
        ex_dividend_date      AS ex_date,
        max(declaration_date) AS declared,
        max(cash_amount)      AS amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'MO'
      AND ex_dividend_date >= toDate('2008-04-01')
    GROUP BY ex_dividend_date
),
labelled AS
(
    SELECT
        ex_date,
        declared,
        amount,
        if(toInt64(amount * 1000) % 10 = 0,
           toString(toDecimal64(toFloat64(amount), 2)),
           toString(toDecimal64(toFloat64(amount), 3))) AS amount_label
    FROM paid
),
steps AS
(
    SELECT
        ex_date,
        declared,
        amount,
        amount_label,
        lagInFrame(amount)       OVER (ORDER BY ex_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prior,
        lagInFrame(amount_label) OVER (ORDER BY ex_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prior_label
    FROM labelled
)
SELECT
    toString(ex_date)                                                          AS ex_dividend_date,
    if(declared > toDate('2000-01-01'),
       concat(formatDateTime(declared, '%b'), ' ', toString(toDayOfMonth(declared)), ', ', toString(toYear(declared))),
       'n/a')                                                                  AS announced,
    concat(formatDateTime(ex_date, '%b'), ' ', toString(toDayOfMonth(ex_date)), ', ', toString(toYear(ex_date))) AS first_ex,
    concat('$', prior_label, ' to $', amount_label)                            AS step,
    round(toFloat64(prior), 3)                                                 AS prior_quarterly,
    round(toFloat64(amount), 3)                                                AS new_quarterly,
    round(100 * (toFloat64(amount) - toFloat64(prior)) / toFloat64(prior), 1)  AS raise_pct
FROM steps
WHERE prior > 0
  AND amount > prior
ORDER BY ex_date