> ## Documentation Index
> Fetch the complete documentation index at: https://fruitstand.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# How to Calculate Rolling Returns and Max Drawdown in SQL

> Calculate rolling returns and maximum drawdown in SQL: tested Snowflake queries for every window, every drawdown with recovery dates, and real fund results.

*By [Fruit Stand Team](https://fruitstand.dev/about.md) · November 19, 2026 · Guides*

A rolling return is the trailing return for every end date, so in SQL you
summarize one window per day. Maximum drawdown is
each day's value divided by its running peak, `max() over (order by date)`,
minus 1, then the minimum. For SPY, the worst rolling 10-year return was
−4.58% a year, and the maximum drawdown −55.2%.

Below are tested queries for both, with results for six funds and ETFs:
how often each holding period lost money, how a fund did against SPY across
every window, and a list of every drawdown with its peak, trough and
recovery dates.

> [!NOTE]
>
> The queries run in Snowflake against `ext_fund_trailing_returns_vw` in
> Fruit Stand's [Fund Returns
> listing](https://app.snowflake.com/marketplace/listing/GZTYZTG3LLO9), which
> we publish. They were run on November 19, 2026, with data through November
> 18\. All returns include reinvested dividends.

## Rolling returns vs. trailing returns

A trailing return covers one window ending today: the 10-year return as of
November 18, 2026. A rolling return repeats that for every end date, then
looks at the spread: the worst window, the median, the best, and how often
the return was negative. One trailing number depends on the day you ask;
rolling returns show how much it could have varied.

| | Trailing return | Rolling returns |
| - | - | - |
| Windows | One, ending on the as-of date | One per end date, often thousands |
| Answers | How did it do over the last N years? | How did it do over any N years? |
| Result | One number | A distribution: worst, median, best, share negative |
| Sensitive to the end date | Very | No, that's the point |

## Rolling returns in SQL

The Fund Returns listing stores trailing returns for every fund for every
trading day, so each row is already one rolling window. The rolling-return
summary is a `group by`:

```sql theme={null}
select code,
       count(return_10y) as windows,
       round(min(return_10y), 4) as worst_10y,
       min_by(as_of_date, return_10y) as worst_ending,
       round(median(return_10y), 4) as median_10y,
       round(max(return_10y), 4) as best_10y,
       max_by(as_of_date, return_10y) as best_ending,
       round(count_if(return_10y < 0) / count(return_10y), 3) as share_negative
from ext_fund_trailing_returns_vw
where code in ('SPY', 'QQQ', 'AGTHX', 'DODGX', 'VBTLX')
  and return_10y is not null
group by code
order by code;
```

| Fund | 10-year windows | Worst (a year) | Worst ended | Median | Best | Best ended | Share negative |
| - | -: | -: | - | -: | -: | - | -: |
| Growth Fund of America (AGTHX) | 9,289 | −0.35% | Aug 31, 2010 | 13.35% | 24.18% | Aug 23, 2000 | 0.1% |
| Dodge & Cox Stock (DODGX) | 8,098 | +1.11% | Mar 9, 2009 | 12.77% | 19.13% | Dec 5, 1997 | 0.0% |
| Invesco QQQ (QQQ) | 4,453 | −8.34% | Mar 9, 2010 | 13.91% | 23.51% | Nov 24, 2021 | 10.8% |
| SPDR S\&P 500 (SPY) | 5,991 | −4.58% | Mar 9, 2009 | 8.78% | 17.40% | Mar 5, 2019 | 8.0% |
| Vanguard Total Bond (VBTLX) | 3,769 | +0.61% | Oct 24, 2022 | 3.69% | 5.60% | Jul 30, 2012 | 0.0% |

10-year returns are annualized. `min_by(as_of_date, return_10y)` returns
the end date of the worst window, so you can see which decade it was. SPY's
worst 10 years ended at the March 2009 low, and QQQ's started near the
March 2000 peak of the dot-com bubble.

The windows aren't the same for every fund: AGTHX's start in 1990 and
QQQ's in 2009, because that's when each fund had 10 years of history. To
compare funds fairly, filter to the same range of end dates, as in the
benchmark query below.

## Longer holding periods aren't always safer

The same summary for SPY's 1-, 3-, 5- and 10-year windows, on the same end
dates (every day from January 2003, when SPY first had a 10-year return).
`unpivot` turns the four columns into rows:

```sql theme={null}
select holding_period,
       round(min(r), 4) as worst,
       round(median(r), 4) as median,
       round(max(r), 4) as best,
       round(count_if(r < 0) / count(*), 3) as share_negative
from (
    select as_of_date, return_1y, return_3y, return_5y, return_10y
    from ext_fund_trailing_returns_vw
    where code = 'SPY' and return_10y is not null
) unpivot (r for holding_period in (return_1y, return_3y, return_5y, return_10y))
group by holding_period
order by len(holding_period), holding_period;
```

| Holding period | Worst | Median | Best | Share of windows negative |
| - | -: | -: | -: | -: |
| 1 year | −47.35% | 14.21% | 77.50% | 15.7% |
| 3 years (a year) | −17.18% | 11.40% | 29.09% | 16.5% |
| 5 years (a year) | −8.22% | 10.86% | 24.85% | 17.4% |
| 10 years (a year) | −4.58% | 8.78% | 17.40% | 8.0% |

The range narrows as the window gets longer, as you'd expect. The share of
losing windows doesn't: since 2003, 5-year windows lost money more often
than 1-year windows did, because so many of them spanned both the 2000–2002
and 2007–2009 bear markets. Rule-of-thumb claims that a longer hold "always"
reduces the chance of a loss depend on the period you measure.

Note that 1-year returns are cumulative and the longer ones are annualized,
which is the listing's convention (see the [dataset
docs](https://fruitstand.dev/datasets/fund-returns.md)). A 5-year window at −8.22% a year lost about
35% in total.

## Daily or monthly windows?

Many fund sites measure rolling returns once a month. You get nearly the
same picture from month-end windows, but you can miss the extremes:

```sql theme={null}
select count(*) as windows,
       round(min(return_10y), 4) as worst,
       round(median(return_10y), 4) as median,
       round(count_if(return_10y < 0) / count(*), 3) as share_negative
from (
    select as_of_date, return_10y
    from ext_fund_trailing_returns_vw
    where code = 'SPY' and return_10y is not null
    qualify as_of_date = max(as_of_date) over (partition by date_trunc(month, as_of_date))
)
```

On 287 month-end windows, SPY's median 10-year return is 8.80% and 8.0% of
windows are negative, the same as with 5,991 daily ones. The worst is
−3.45% a year rather than −4.58%: the worst window ended on March 9, 2009,
mid-month. Daily windows overlap almost entirely, so they don't add
independent evidence; they just make sure the worst and best days are in.

## Rolling returns against a benchmark

Join each fund's row to SPY's row for the same date and you get the excess
return for every window, which says more than a single trailing comparison:

```sql theme={null}
select f.code,
       count(*) as windows,
       round(avg(f.return_10y - b.return_10y), 4) as avg_excess,
       round(min(f.return_10y - b.return_10y), 4) as worst_excess,
       round(max(f.return_10y - b.return_10y), 4) as best_excess,
       round(count_if(f.return_10y > b.return_10y) / count(*), 3) as share_ahead
from ext_fund_trailing_returns_vw f
join ext_fund_trailing_returns_vw b
  on b.code = 'SPY' and b.as_of_date = f.as_of_date
where f.code in ('AGTHX', 'DODGX', 'QQQ')
  and f.return_10y is not null and b.return_10y is not null
group by f.code
order by f.code;
```

| Fund | 10-year windows | Average excess (a year) | Worst | Best | Share of windows ahead of SPY |
| - | -: | -: | -: | -: | -: |
| AGTHX | 5,991 | +1.69 | −1.14 | +7.04 | 81.8% |
| DODGX | 5,986 | +1.40 | −3.10 | +7.47 | 52.5% |
| QQQ | 4,453 | +3.62 | −8.03 | +7.40 | 88.2% |

Excess returns are in percentage points a year. Dodge & Cox Stock was ahead
of SPY by 1.4 points a year on average, but only in about half the windows:
large wins in some decades, losses in others. Growth Fund of America was
ahead less dramatically but far more often. The join on `as_of_date` keeps
both sides on the same windows. For the single-date version, see [How to
Compare a Fund's Returns With Its Benchmark in
SQL](https://fruitstand.dev/blog/compare-fund-returns-benchmark-sql.md).

If your data has prices rather than ready-made returns, compute the window
yourself: [How to Calculate Stock Returns From Daily Prices in
SQL](https://fruitstand.dev/blog/calculate-stock-returns-sql.md) matches each day with the trading
day a year earlier using `asof join`.

## Maximum drawdown in SQL

Drawdown needs a value series, not a return. Rebuild one from the daily
returns by compounding them, `exp(sum(ln(1 + r)))` as a running sum, then
compare each day with the highest value so far:

```sql theme={null}
with growth as (
    select code, as_of_date,
           exp(sum(ln(1 + coalesce(return_1d, 0))) over (partition by code order by as_of_date)) as value
    from ext_fund_trailing_returns_vw
    where code in ('SPY', 'QQQ', 'AGTHX', 'DODGX', 'VBTLX', 'TLT')
      and as_of_date >= '2006-12-29'
),
drawdowns as (
    select code, as_of_date,
           value / max(value) over (partition by code order by as_of_date) - 1 as drawdown
    from growth
)
select code,
       round(min(drawdown), 4) as max_drawdown,
       min_by(as_of_date, drawdown) as trough_date,
       round(max_by(drawdown, as_of_date), 4) as current_drawdown,
       max(iff(drawdown = 0, as_of_date, null)) as last_peak
from drawdowns
group by code
order by max_drawdown;
```

| Fund | Max drawdown since 2007 | Trough | Below peak on Nov 18, 2026 | Last peak |
| - | -: | - | -: | - |
| Dodge & Cox Stock (DODGX) | −63.24% | Mar 9, 2009 | −4.08% | Aug 14, 2026 |
| SPDR S\&P 500 (SPY) | −55.19% | Mar 9, 2009 | −6.28% | Oct 6, 2026 |
| Invesco QQQ (QQQ) | −53.40% | Nov 20, 2008 | −13.18% | Oct 6, 2026 |
| Growth Fund of America (AGTHX) | −51.65% | Mar 9, 2009 | −9.88% | Oct 6, 2026 |
| iShares 20+ Year Treasury (TLT) | −48.35% | Oct 19, 2023 | −42.87% | Aug 4, 2020 |
| Vanguard Total Bond (VBTLX) | −18.68% | Oct 24, 2022 | −3.70% | Aug 4, 2020 |

The value series starts at about 1 and only its ratios matter, so there's
no need for a price. `coalesce` covers the null 1-day return on a fund's
first date. We checked SPY, QQQ, DODGX and TLT against their adjusted
closes in our [Historical Stock Prices](https://fruitstand.dev/datasets/historical-stock-prices.md)
listing; the drawdowns match to the hundredth of a point.

The longest drawdown on this list isn't a stock fund's. A long-term
Treasury ETF, TLT, fell 48% from August 2020 to October 2023 and
was still 43% below its peak in November 2026, more than six years later.
VBTLX, the broad bond market, has also not made a new high since August
2020\.

## Every drawdown, with recovery dates

The maximum is one number. To list every drawdown, number the episodes:
each day the fund is at a new high (`drawdown = 0`) starts a new one, so a
running `count_if` labels them. The first day of the next episode is the
day the previous one recovered:

```sql theme={null}
with growth as (
    select code, as_of_date,
           exp(sum(ln(1 + coalesce(return_1d, 0))) over (partition by code order by as_of_date)) as value
    from ext_fund_trailing_returns_vw
    where code = 'SPY'
),
drawdowns as (
    select code, as_of_date,
           value / max(value) over (partition by code order by as_of_date) - 1 as drawdown
    from growth
),
episodes as (
    select *, count_if(drawdown = 0) over (partition by code order by as_of_date) as episode
    from drawdowns
),
summary as (
    select code, episode,
           min(as_of_date) as peak_date,
           min_by(as_of_date, drawdown) as trough_date,
           min(drawdown) as max_drawdown,
           lead(min(as_of_date)) over (partition by code order by episode) as recovered_on
    from episodes
    group by code, episode
)
select peak_date, trough_date, round(max_drawdown, 4) as max_drawdown, recovered_on,
       datediff(day, peak_date, trough_date) as days_falling,
       datediff(day, trough_date, recovered_on) as days_recovering
from summary
where max_drawdown < -0.15
order by peak_date;
```

| Peak | Trough | Drawdown | Back to the peak | Days falling | Days recovering |
| - | - | -: | - | -: | -: |
| Jul 20, 1998 | Aug 31, 1998 | −19.03% | Nov 23, 1998 | 42 | 84 |
| Mar 24, 2000 | Oct 9, 2002 | −47.51% | Oct 26, 2006 | 929 | 1,478 |
| Oct 9, 2007 | Mar 9, 2009 | −55.19% | Aug 16, 2012 | 517 | 1,256 |
| Sep 20, 2018 | Dec 24, 2018 | −19.35% | Apr 12, 2019 | 95 | 109 |
| Feb 19, 2020 | Mar 23, 2020 | −33.72% | Aug 10, 2020 | 33 | 140 |
| Jan 3, 2022 | Oct 12, 2022 | −24.50% | Dec 13, 2023 | 282 | 427 |
| Feb 19, 2025 | Apr 8, 2025 | −18.76% | Jun 26, 2025 | 48 | 79 |

These are SPY's drawdowns of more than 15% since 1993, with dividends
reinvested. Recovery usually takes longer than the fall: after 2009 it took
almost three and a half years, and after 2000 about four.

Dividends make a difference that drawdown tables usually leave out. They
quote the S\&P 500 price index, which didn't close above its October 2007 high
until [March 28,
2013](https://money.cnn.com/2013/03/28/investing/stocks-markets/). With
dividends reinvested, SPY was back on August 16, 2012, more than seven
months earlier, and its drawdown was −55.2% rather than the index's −56.8%.
For an investor who reinvests, the total-return series is the one that
counts.

Change `where max_drawdown < -0.15` to list smaller drawdowns, or drop the
`code` filter to list them for every fund. Episodes are grouped by `code`,
so the query works across funds unchanged.

## Mistakes that give wrong numbers

* **Using prices without dividends.** A price-only series overstates
  drawdowns and recovery times, as above. Use adjusted prices or total
  returns.
* **Leaving out `partition by code`.** The running peak of one fund
  carries into the next, and every drawdown after the first fund is wrong.
* **Comparing funds over different periods.** A fund with a longer history
  includes more crashes. Start every fund on the same date, as the
  drawdown query does from 2007.
* **Sampling monthly and calling it the maximum.** Month-end data misses
  troughs between month-ends. SPY's month-end 10-year windows miss its worst
  one.
* **Mixing annualized and cumulative returns.** A −8% a year 5-year return
  is a −35% loss in total; label which one a table shows.

## Limits of this approach

* **Fund returns are end-of-day.** Intraday lows are lower than any
  close, so an intraday drawdown would be deeper.
* **Thinly traded funds.** When a fund doesn't trade, the listing
  measures from the last date it did, so one return can span more than one
  day (see the [dataset docs](https://fruitstand.dev/datasets/fund-returns.md)). Peak and trough
  dates for those funds are only as exact as their trading.
* **ETF returns use market prices; mutual fund returns use NAV.** For
  most large ETFs the difference is small, but it can matter in a crisis.

## F.A.Q.

> [accordion] What's the difference between rolling and trailing returns?
>
> A trailing return is one window ending on a given date, such as the 10-year return as of today. Rolling returns are that window computed for every end date, summarized as the worst, median and best window and how often it was negative.

> [accordion] How do you calculate maximum drawdown?
>
> Build a value series that includes dividends, divide each day's value by the highest value up to that day, and subtract 1. The most negative result is the maximum drawdown. In SQL the running peak is `max(value) over (partition by code order by date)`.

> [accordion] Can I calculate max drawdown from returns instead of prices?
>
> Yes. Compound the daily returns into a value series with `exp(sum(ln(1 + r)) over (order by date))`, then use the running peak on that. It gives the same drawdown as adjusted prices, because only the ratios matter.

> [accordion] How do I find how long a drawdown lasted?
>
> Label episodes with a running `count_if(drawdown = 0)`, so each new high starts a new episode. The first date of the next episode is the recovery date, and `datediff` gives the days from peak to trough and from trough to recovery.

> [accordion] Should rolling returns use daily or monthly windows?
>
> Either gives a similar median and share of negative windows. Daily windows include the worst and best days, which month-end windows can miss; on SPY's 10-year returns, the worst daily window was −4.58% a year against −3.45% for month-ends.

To run these queries on about 32,000 US mutual funds and ETFs, start a free
30-day trial of the [Fund Returns listing on the Snowflake
Marketplace](https://app.snowflake.com/marketplace/listing/GZTYZTG3LLO9). It
has 1-day to 20-year trailing returns for every trading day and
calendar-year returns. The schema is in the [dataset
docs](https://fruitstand.dev/datasets/fund-returns.md). For drawdowns in a trading strategy, see [How
to Backtest a Trading Strategy in Snowflake
SQL](https://fruitstand.dev/blog/backtest-trading-strategy-snowflake.md). Questions? Reach out at
[contact@fruitstand.dev](mailto:contact@fruitstand.dev).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.