STRASMORE/EXPLORE 3,256 QUERIES 22Y EQUITIES · 12Y OPTIONS

3,256 answered market questions

every one with its exact SQL, its result and the date it was computed · free, no signup

Is the Market a Random Walk? Real Returns
Deepest fall: 400 matched random walks versus SPYranking · 2026-10-01 · 5×4Preview: 5 ranked values, smallest first. Daily moves by sigma distance: counted versus a normal modelranking · 2026-10-01 · 6×4Preview: 6 ranked values, largest first. Consecutive down days: counted versus a fair cointable · 2026-10-01 · 7×6 Excess kurtosis by year, against the full window levelranking · 2026-10-01 · 21×4Preview: 16 ranked values, smallest first. Autocorrelation of returns and absolute returns, lags 1 to 10ranking · 2026-10-01 · 10×3Preview: 10 ranked values, smallest first.
Bootstrapping Backtest Confidence Bands
Variance ratio by block length: does SPY variance scale like independent draws?ranking · 2026-08-14 · 7×3Preview: 7 ranked values, largest first. One position, one year at a time: SPY annualized Sharpe by calendar yeartable · 2026-08-14 · 14×5 Measured Sharpe dispersion across non-overlapping SPY windows, 2006 to 2025table · 2026-08-14 · 5×6 Lag-one autocorrelation: signed returns against absolute returns, 2016 to 2025ranking · 2026-08-14 · 6×4Preview: 6 ranked values, largest first.
Pairs Trading and Cointegration Explained
Weekly z score of the KO/PEP spread, hedge ratio fitted on 2023 onlyseries · 2026-08-13 · 105×2Preview: a 16-point series, ending higher. Where the KO/PEP spread sat twenty sessions later, by starting z score (2019-2025)ranking · 2026-08-13 · 6×4Preview: 6 ranked values, smallest first. Daily-return correlation vs price-level correlation, five familiar pairs (2024-2025)ranking · 2026-08-13 · 5×3Preview: 5 ranked values, largest first. Hedge ratio refitted each calendar year, two sector pairsranking · 2026-08-13 · 7×3Preview: 7 ranked values, smallest first.
Deepest fall: 400 matched random walks versus SPY

Deepest fall: 400 matched random walks versus SPY

most recentas of ranking 5×4read in context →
Deepest fall: 400 matched random walks versus SPY — 5 rows by 4 columns, computed from US exchange, SIP and OPRA data.
percentilesim_drawdown_depth_pctspy_drawdown_depth_pctpaths_at_least_as_deep_pct
p0531.856.514
p2537.956.514
p504556.514
p7551.556.514
p9563.356.514
the exact SQL behind every number 
WITH daily AS (SELECT date, argMax(toFloat64(close), _ingest_time) AS px FROM global_markets.stocks_daily_aggs WHERE ticker = 'SPY' AND date >= '2006-01-01' AND date <= '2026-09-30' GROUP BY date),
rets AS (SELECT date, px / prev_px - 1 AS ret FROM (SELECT date, px, lagInFrame(px) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_px FROM daily) WHERE prev_px > 0),
stats AS (SELECT avg(ret) AS mu, stddevPop(ret) AS sd, count() AS n FROM rets),
real_curve AS (SELECT date, sum(log(1 + ret)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS log_equity FROM rets),
real_depth AS (SELECT 100 * (1 - exp(min(log_equity - greatest(peak, 0.0)))) AS depth_pct FROM (SELECT log_equity, max(log_equity) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak FROM real_curve)),
steps AS (SELECT pp.p AS path_id, arrayJoin(range(toUInt32(s.n))) AS t, s.mu AS mu, s.sd AS sd FROM stats AS s CROSS JOIN (SELECT arrayJoin(range(400)) AS p) AS pp),
draws AS (SELECT path_id, t, mu + sd * sqrt(-2 * log((cityHash64('strasmore-walk', path_id, t) % 1000000 + 0.5) / 1000000)) * cos(2 * pi() * ((cityHash64('strasmore-phase', t, path_id) % 1000000 + 0.5) / 1000000)) AS ret FROM steps),
sim_curve AS (SELECT path_id, t, sum(log(1 + ret)) OVER (PARTITION BY path_id ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS log_equity FROM draws),
sim_depth AS (SELECT path_id, 100 * (1 - exp(min(log_equity - greatest(peak, 0.0)))) AS depth_pct FROM (SELECT path_id, log_equity, max(log_equity) OVER (PARTITION BY path_id ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak FROM sim_curve) GROUP BY path_id),
summary AS (SELECT quantilesDeterministic(0.05, 0.25, 0.5, 0.75, 0.95)(d.depth_pct, d.path_id) AS qs, round(any(r.depth_pct), 1) AS spy_depth, round(100 * countIf(d.depth_pct >= r.depth_pct) / count(), 1) AS deeper_share FROM sim_depth AS d CROSS JOIN real_depth AS r)
SELECT
    tupleElement(tagged, 1)           AS percentile,
    round(tupleElement(tagged, 2), 1) AS sim_drawdown_depth_pct,
    spy_depth                         AS spy_drawdown_depth_pct,
    deeper_share                      AS paths_at_least_as_deep_pct
FROM (SELECT arrayJoin(arrayZip(['p05', 'p25', 'p50', 'p75', 'p95'], qs)) AS tagged, spy_depth, deeper_share FROM summary)
More from this analysisIs the Market a Random Walk? Real Returns
Excess kurtosis by year, against the full window level ranking 21×4 → Autocorrelation of returns and absolute returns, lags 1 to 10 ranking 10×3 → Daily moves by sigma distance: counted versus a normal model ranking 6×4 → Consecutive down days: counted versus a fair coin table 7×6 → See all 3,256 queries →
$