Declared vs. still expected: this month in each of the last six years

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 changes AS
(
    SELECT
        ticker,
        ex_dividend_date,
        declaration_date,
        amount,
        prev_amount,
        (prev_amount > 0 AND amount > prev_amount
             AND declaration_date > toDate('2000-01-01'))            AS is_raise,
        (prev_amount > 0 AND amount < prev_amount)                   AS is_cut,
        round((amount / prev_amount - 1) * 100, 1)                   AS raise_pct
    FROM
    (
        SELECT
            ticker,
            ex_dividend_date,
            declaration_date,
            amount,
            lagInFrame(amount, 1) OVER (PARTITION BY ticker ORDER BY ex_dividend_date
                                        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_amount
        FROM
        (
            SELECT
                ticker,
                ex_dividend_date,
                ifNull(max(declaration_date), toDate('1970-01-01')) AS declaration_date,
                ifNull(max(toFloat64(cash_amount)), 0)              AS amount
            FROM global_markets.stocks_dividends
            WHERE toString(frequency) = '4'
              AND ex_dividend_date >= toDate('2020-01-01')
              AND ticker NOT IN ('SPCX')
            GROUP BY ticker, ex_dividend_date
        )
    )
),
qualifiers AS
(
    SELECT ticker
    FROM changes
    GROUP BY ticker
    HAVING uniqExactIf(toYear(declaration_date),
                       is_raise AND (toYear(declaration_date) BETWEEN toYear(today()) - 5 AND toYear(today()) - 1)) = 5
       AND uniqExactIf(toYear(declaration_date),
                       is_raise AND (toYear(declaration_date) BETWEEN toYear(today()) - 5 AND toYear(today()) - 1)
                                AND toMonth(declaration_date) = toMonth(today())) >= 4
       AND countIf(is_cut AND ex_dividend_date >= addYears(toStartOfYear(today()), -5)) = 0
)
SELECT
    spine.year                                                        AS year,
    ifNull(r.names_declared, 0)                                       AS declared_in_month,
    (SELECT count() FROM qualifiers) - ifNull(r.names_declared, 0)    AS not_declared_in_month,
    ifNull(r.median_raise_pct, 0)                                     AS median_raise_pct
FROM
(
    SELECT toUInt16(toYear(today()) - 5 + arrayJoin(range(6))) AS year
) AS spine
LEFT JOIN
(
    SELECT
        toUInt16(toYear(declaration_date))          AS year,
        uniqExact(ticker)                           AS names_declared,
        round(quantileExact(0.5)(raise_pct), 1)     AS median_raise_pct
    FROM changes
    WHERE is_raise
      AND toMonth(declaration_date) = toMonth(today())
      AND ticker IN (SELECT ticker FROM qualifiers)
    GROUP BY year
) AS r ON r.year = spine.year
ORDER BY year