How far SPY travels over one session, and over sixty three

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
    horizon,
    round(sqrt(arrayReduce('varPop', returns)) * 100, 2)           AS move_pct,
    round(sqrt(arrayReduce('varPop', returns)) * 100 / sqrt(h), 2) AS daily_equivalent_pct,
    length(returns)                                                AS sample_count
FROM
(
    SELECT
        hz.h                                                          AS h,
        concat(toString(hz.h), if(hz.h = 1, ' session', ' sessions')) AS horizon,
        arrayMap((later, earlier) -> (later / earlier) - 1,
                 arraySlice(spy.closes, hz.h + 1),
                 arraySlice(spy.closes, 1, length(spy.closes) - hz.h)) AS returns
    FROM
    (
        SELECT arrayJoin([1, 2, 5, 10, 21, 42, 63]) AS h
    ) AS hz
    CROSS JOIN
    (
        SELECT arrayMap(t -> t.2, arraySort(t -> t.1, groupArray((session_date, close_px)))) AS closes
        FROM
        (
            SELECT
                date                   AS session_date,
                toFloat64(max(close))  AS close_px
            FROM global_markets.stocks_daily_aggs
            WHERE ticker = 'SPY'
              AND date >= today() - 1100
              AND date <  today() - 1
            GROUP BY session_date
        )
    ) AS spy
)
WHERE length(returns) > 100
ORDER BY h