fifteen_year_windows

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,
        argMax(toFloat64(close), date) AS month_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2003-01-01'
    GROUP BY month_start
),
windows AS
(
    SELECT later.month_close / earlier.start_close AS multiple
    FROM monthly AS later
    INNER JOIN
    (
        SELECT
            addYears(month_start, 15) AS month_start,
            month_close               AS start_close
        FROM monthly
    ) AS earlier USING (month_start)
)
SELECT
    multiIf(multiple < 1, 'below the strike',
            multiple < 2, '1x to 2x the strike',
            multiple < 3, '2x to 3x the strike',
            multiple < 4, '3x to 4x the strike',
            multiple < 5, '4x to 5x the strike',
                          '5x or more') AS outcome_bucket,
    count()                             AS window_count,
    round(min(multiple), 2)             AS lowest_multiple,
    round(max(multiple), 2)             AS highest_multiple
FROM windows
GROUP BY outcome_bucket
ORDER BY lowest_multiple