bear_markets

Name: bear_markets
Creator: Strasmore
Published: 2026-09-20
License: https://www.strasmore.com/terms/

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-09-20, from bullish-vs-bearish-meaning.

as of 2026-09-20 table 4×6read in context →

bear_markets — 4 rows by 6 columns, computed 2026-09-20 from US exchange, SIP and OPRA data.
peak	trough	decline_pct	days_top_to_bottom	new_high	days_bottom_to_new_high
Oct 2007	Mar 2009	-56.5	517	Mar 2013	1466
Feb 2020	Mar 2020	-34.1	33	Aug 2020	148
Jan 2022	Oct 2022	-25.4	282	Jan 2024	464
Sep 2018	Dec 2018	-20.2	95	Apr 2019	126

Rows × columns: 4 × 6
Computed: 2026-09-20
Completeness: No missing values
Source: US exchange, SIP and OPRA market data
Licence: Strasmore terms · free, no signup
Formats: JSON · CSV · the SQL below

What each column holds

Column definitions for bear_markets, derived from the stored result.
Column	Type	Range	Notes
`peak`	text	4 distinct values (Feb 2020, Jan 2022, Oct 2007…)
`trough`	text	4 distinct values (Dec 2018, Mar 2009, Mar 2020…)
`decline_pct`	number	-56.5 to -20.2	percent
`days_top_to_bottom`	number	33 to 517
`new_high`	text	4 distinct values (Apr 2019, Aug 2020, Jan 2024…)
`days_bottom_to_new_high`	number	126 to 1,466	US dollars

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 2026-09-20, 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 bars AS
(
    SELECT
        date,
        toFloat64(any(close)) AS close_price
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND close > 0
    GROUP BY date
),
peaks AS
(
    SELECT
        date,
        close_price,
        max(close_price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak
    FROM bars
),
episodes AS
(
    SELECT
        date,
        close_price,
        peak,
        close_price / peak - 1 AS drawdown,
        sum(if(close_price >= peak, 1, 0)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS episode_id
    FROM peaks
),
episode_stats AS
(
    SELECT
        episode_id,
        min(date)               AS peak_date,
        argMin(date, drawdown)  AS trough_date,
        min(drawdown)           AS worst_drawdown
    FROM episodes
    GROUP BY episode_id
),
with_next AS
(
    SELECT
        episode_id,
        peak_date,
        trough_date,
        worst_drawdown,
        leadInFrame(peak_date, 1) OVER (ORDER BY episode_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS recovered_on
    FROM episode_stats
)
SELECT
    formatDateTime(peak_date, '%b %Y')                                                AS peak,
    formatDateTime(trough_date, '%b %Y')                                              AS trough,
    round(100 * worst_drawdown, 1)                                                    AS decline_pct,
    dateDiff('day', peak_date, trough_date)                                           AS days_top_to_bottom,
    if(recovered_on > peak_date, formatDateTime(recovered_on, '%b %Y'), 'not yet')    AS new_high,
    if(recovered_on > peak_date, dateDiff('day', trough_date, recovered_on), NULL)    AS days_bottom_to_new_high
FROM with_next
WHERE worst_drawdown <= -0.20
ORDER BY worst_drawdown ASC

SQL to run

WITH bars AS
(
    SELECT
        date,
        toFloat64(any(close)) AS close_price
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND close > 0
    GROUP BY date
),
peaks AS
(
    SELECT
        date,
        close_price,
        max(close_price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak
    FROM bars
),
episodes AS
(
    SELECT
        date,
        close_price,
        peak,
        close_price / peak - 1 AS drawdown,
        sum(if(close_price >= peak, 1, 0)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS episode_id
    FROM peaks
),
episode_stats AS
(
    SELECT
        episode_id,
        min(date)               AS peak_date,
        argMin(date, drawdown)  AS trough_date,
        min(drawdown)           AS worst_drawdown
    FROM episodes
    GROUP BY episode_id
),
with_next AS
(
    SELECT
        episode_id,
        peak_date,
        trough_date,
        worst_drawdown,
        leadInFrame(peak_date, 1) OVER (ORDER BY episode_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS recovered_on
    FROM episode_stats
)
SELECT
    formatDateTime(peak_date, '%b %Y')                                                AS peak,
    formatDateTime(trough_date, '%b %Y')                                              AS trough,
    round(100 * worst_drawdown, 1)                                                    AS decline_pct,
    dateDiff('day', peak_date, trough_date)                                           AS days_top_to_bottom,
    if(recovered_on > peak_date, formatDateTime(recovered_on, '%b %Y'), 'not yet')    AS new_high,
    if(recovered_on > peak_date, dateDiff('day', trough_date, recovered_on), NULL)    AS days_bottom_to_new_high
FROM with_next
WHERE worst_drawdown <= -0.20
ORDER BY worst_drawdown ASC

⌘/Ctrl + Enter