Where the seven-year windows landed: SPY windows by return band, dividends counted

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
    monthly AS
    (
        SELECT
            toStartOfMonth(date)            AS month_start,
            toDate(min(date))               AS first_session,
            argMin(toFloat64(close), date)  AS first_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
        GROUP BY month_start
    ),
    divs AS
    (
        SELECT
            toStartOfMonth(ex_dividend_date) AS month_start,
            sum(cash)                        AS month_cash
        FROM
        (
            SELECT
                ex_dividend_date,
                max(toFloat64(cash_amount)) AS cash
            FROM global_markets.stocks_dividends
            WHERE ticker = 'SPY'
            GROUP BY ex_dividend_date
        )
        GROUP BY month_start
    ),
    grid AS
    (
        SELECT
            m.month_start                AS month_start,
            m.first_session              AS first_session,
            m.first_close                AS first_close,
            addMonths(m.month_start, 84) AS end_month,
            sum(ifNull(d.month_cash, 0)) OVER (ORDER BY m.month_start ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS cash_before
        FROM monthly AS m
        LEFT JOIN divs AS d ON d.month_start = m.month_start
    ),
    windows AS
    (
        SELECT
            ((e.first_close + e.cash_before - s.cash_before) / s.first_close - 1) * 100 AS ret
        FROM grid AS s
        INNER JOIN grid AS e ON e.month_start = s.end_month
    ),
    stats AS
    (
        SELECT
            count()                           AS total,
            countIf(ret < 0)                  AS below_zero,
            countIf(ret >= 0  AND ret < 25)   AS to_25,
            countIf(ret >= 25 AND ret < 50)   AS to_50,
            countIf(ret >= 50 AND ret < 100)  AS to_100,
            countIf(ret >= 100)               AS above_100
        FROM windows
    )
SELECT
    tupleElement(band, 1) AS bucket,
    tupleElement(band, 2) AS window_count,
    tupleElement(band, 3) AS share_pct
FROM stats
ARRAY JOIN
    [
        ('Below 0%',    below_zero, round(below_zero / total * 100, 1)),
        ('0% to 25%',   to_25,      round(to_25 / total * 100, 1)),
        ('25% to 50%',  to_50,      round(to_50 / total * 100, 1)),
        ('50% to 100%', to_100,     round(to_100 / total * 100, 1)),
        ('Above 100%',  above_100,  round(above_100 / total * 100, 1))
    ] AS band