run_lengths
Answered against 22 years of US equities and 12 years of US options data and published with the query that produced it. This result is stored as of 2026-10-01, from real-returns-vs-random-walks.
| down_run_length | spy_runs | coin_flip_runs | down_day_share_pct | sample_from | sample_to |
|---|---|---|---|---|---|
| 1 | 728 | 707.4 | 45.3 | January 4, 2006 | September 30, 2026 |
| 2 | 321 | 320.3 | 45.3 | January 4, 2006 | September 30, 2026 |
| 3 | 153 | 145 | 45.3 | January 4, 2006 | September 30, 2026 |
| 4 | 68 | 65.6 | 45.3 | January 4, 2006 | September 30, 2026 |
| 5 | 30 | 29.7 | 45.3 | January 4, 2006 | September 30, 2026 |
| 6 | 10 | 13.5 | 45.3 | January 4, 2006 | September 30, 2026 |
| 7 plus | 7 | 11.1 | 45.3 | January 4, 2006 | September 30, 2026 |
- Rows × columns
- 7 × 6
- Computed
- Completeness
- No missing values
- Source
- US exchange, SIP and OPRA market data
- Licence
- Strasmore terms · free, no signup
What each column holds
| Column | Type | Range | Notes |
|---|---|---|---|
down_run_length |
text | 7 distinct values (1, 2, 3…) | |
spy_runs |
number | 7 to 728 | |
coin_flip_runs |
number | 11.1 to 707.4 | |
down_day_share_pct |
number | every row is 45.3 | percent |
sample_from |
text | 1 distinct value (January 4, 2006) | |
sample_to |
text | 1 distinct value (September 30, 2026) |
Computed from Strasmore's warehouse of US exchange, SIP and OPRA market data. Equity prices are delayed; options greeks and implied volatility are end-of-day. This result is stored, not recomputed on load — it is exactly the numbers that were returned on , and the query below is what returned them.
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 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),
flags AS (SELECT date, if(ret < 0, 1, 0) AS down FROM rets),
islands AS (SELECT down, row_number() OVER (ORDER BY date) - sum(down) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island FROM flags),
runs AS (SELECT island, count() AS run_length FROM islands WHERE down = 1 GROUP BY island),
sample AS (SELECT count() AS n, avg(down) AS p, concat(monthName(min(date)), ' ', toString(toDayOfMonth(min(date))), ', ', toString(toYear(min(date)))) AS sample_from, concat(monthName(max(date)), ' ', toString(toDayOfMonth(max(date))), ', ', toString(toYear(max(date)))) AS sample_to FROM flags)
SELECT
if(bucket = 7, '7 plus', toString(bucket)) AS down_run_length,
count() AS spy_runs,
round(if(bucket = 7, any(n) * pow(any(p), 7) * (1 - any(p)), any(n) * pow(any(p), bucket) * pow(1 - any(p), 2)), 1) AS coin_flip_runs,
round(100 * any(p), 1) AS down_day_share_pct,
any(sample_from) AS sample_from,
any(sample_to) AS sample_to
FROM (SELECT least(r.run_length, 7) AS bucket, s.n AS n, s.p AS p, s.sample_from AS sample_from, s.sample_to AS sample_to FROM runs AS r CROSS JOIN sample AS s)
GROUP BY bucket
ORDER BY bucket
Work with this data in your AI assistant
Opens ready to query, with this page's data. Free, no account.