Skip to main content
By Fruit Stand Team · 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.
The queries run in Snowflake against ext_fund_trailing_returns_vw in Fruit Stand’s Fund Returns listing, 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.

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:
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:
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). 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:
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:
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. If your data has prices rather than ready-made returns, compute the window yourself: How to Calculate Stock Returns From Daily Prices in SQL 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:
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 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:
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. 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). 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.

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.
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).
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.
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.
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. It has 1-day to 20-year trailing returns for every trading day and calendar-year returns. The schema is in the dataset docs. For drawdowns in a trading strategy, see How to Backtest a Trading Strategy in Snowflake SQL. Questions? Reach out at contact@fruitstand.dev.