What the lookback window costs in daily turnover (SPY, 10% target, 2x cap)

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
    px AS
    (
        SELECT
            date                  AS d,
            toFloat64(any(close)) AS c
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= subtractYears(today(), 5)
          AND date <  today()
        GROUP BY d
    ),
    px_sorted AS
    (
        SELECT arraySort(p -> p.1, groupArray((d, c))) AS pts
        FROM px
    ),
    rets AS
    (
        SELECT arrayFilter(x -> abs(x) < 0.4,
                   arrayMap((a, b) -> log(b.2 / a.2),
                            arraySlice(pts, 1, length(pts) - 1),
                            arraySlice(pts, 2))) AS r
        FROM px_sorted
    ),
    grid AS
    (
        SELECT
            r,
            arrayJoin([10, 21, 63, 126]) AS lb
        FROM rets
    ),
    weights AS
    (
        SELECT
            lb,
            arrayMap(i -> least(2.0,
                        0.10 / (arrayReduce('stddevSamp', arraySlice(r, i - lb + 1, lb)) * sqrt(252))),
                     range(lb, length(r) + 1)) AS w
        FROM grid
    )
SELECT
    concat(toString(lb), '-session') AS lookback_window,
    round(arrayAvg(w), 2)            AS avg_weight,
    round(100 * arrayAvg(arrayMap((a, b) -> abs(b - a),
                                  arraySlice(w, 1, length(w) - 1),
                                  arraySlice(w, 2))), 2) AS avg_daily_turnover_pct,
    round(100.0 * arrayCount(x -> x > 1.999, w) / length(w), 1) AS days_at_cap_pct
FROM weights
ORDER BY lb