How to Compare a Fund's Returns With Its Benchmark in SQL

By Fruit Stand Team · November 3, 2026 · Guides To compare a fund’s returns with its benchmark in SQL, join the fund’s trailing returns to the benchmark’s for the same as-of date and subtract. With ready-made 1- to 10-year returns, such as Fruit Stand’s Fund Returns listing on Snowflake, it’s one query. Against SPY, Fidelity Contrafund was ahead by 2.5 points a year over 10 years. Below is the query, real results for three well-known active funds, and the things that make a comparison fair or misleading.

The query

Don’t have fund returns in Snowflake yet? Where to Get Trailing Returns for Every US Fund and ETF in Bulk compares the options, from licensed feeds to calculating them yourself. Trailing returns are one row per fund per as-of date, so the comparison joins the fund’s row to the benchmark’s row for the same date:

with latest as (
    select max(as_of_date) as as_of_date
    from ext_fund_trailing_returns_vw
    where code = 'SPY'
),
fund as (
    select r.*
    from ext_fund_trailing_returns_vw r
    join latest l on r.as_of_date = l.as_of_date
    where r.code in ('FCNTX', 'DODGX', 'PRGFX')
),
bench as (
    select r.*
    from ext_fund_trailing_returns_vw r
    join latest l on r.as_of_date = l.as_of_date
    where r.code = 'SPY'
)
select
    f.code,
    f.as_of_date,
    round(100 * (f.return_1y - b.return_1y), 2) as excess_1y,
    round(100 * (f.return_3y - b.return_3y), 2) as excess_3y,
    round(100 * (f.return_5y - b.return_5y), 2) as excess_5y,
    round(100 * (f.return_10y - b.return_10y), 2) as excess_10y
from fund f
cross join bench b
order by excess_10y desc;

Periods of 2 years and longer are annualized, so the 5- and 10-year differences are percentage points a year. Shorter periods are cumulative.

Real results

Trailing returns as of October 30, 2026:

Fund	1 year	3 years	5 years	10 years
SPY (S&P 500 benchmark)	12.36%	23.54%	11.96%	15.32%
Fidelity Contrafund (FCNTX)	12.08%	27.15%	13.29%	17.82%
T. Rowe Price Growth Stock (PRGFX)	11.08%	28.85%	13.05%	21.92%
Dodge & Cox Stock (DODGX)	14.05%	17.91%	9.52%	12.82%

And the difference from SPY, in percentage points:

Fund	1 year	3 years	5 years	10 years
FCNTX	−0.28	+3.61	+1.33	+2.49
PRGFX	−1.28	+5.31	+1.09	+6.60
DODGX	+1.68	−5.63	−2.44	−2.50

Two growth funds beat the S&P 500 over 3, 5 and 10 years but trailed it over the last year. Dodge & Cox, a value fund, did the opposite: ahead over 1 year, behind over longer periods. One number never tells the whole story, which is why comparing every period side by side is worth the extra columns. These are returns calculated from adjusted prices, so they include reinvested distributions and are after the fund’s expenses. They aren’t after sales charges or taxes.

Picking the right benchmark

Match the fund’s style. The S&P 500 is fine for a large-cap blend fund. A growth fund is better compared with a growth index ETF such as IWF, and a value fund with IWD. Comparing Dodge & Cox to the S&P 500 makes a value fund look worse in years growth stocks led.
Use an investable benchmark. An index ETF like SPY has its own small expense ratio, which makes the comparison against a real alternative, not a theoretical index.
Use the same as-of date. The query joins on as_of_date for this reason. Comparing a fund’s month-end return with a benchmark’s latest return mixes two different windows.
Check the fund’s history. A fund younger than the period has no return for it. Filter out nulls rather than comparing against zero.

Comparing across calendar years

Trailing returns move every day. To see how often a fund beat its benchmark, compare calendar years instead:

select
    f.calendar_year,
    round(100 * f.calendar_year_return, 2) as fund_return,
    round(100 * b.calendar_year_return, 2) as spy_return,
    round(100 * (f.calendar_year_return - b.calendar_year_return), 2) as difference
from ext_fund_calendar_year_returns_vw f
join ext_fund_calendar_year_returns_vw b
    on b.calendar_year = f.calendar_year
   and b.code = 'SPY'
where f.code = 'FCNTX'
  and f.calendar_year between 2016 and 2025
order by f.calendar_year;

Count the positive rows and you have how many of the last ten years the fund won. For Contrafund it’s 6 of 10, from 14.15 points ahead in 2020 to 9.30 points behind in 2022, which is why a 10-year average alone hides how bumpy the ride was.

F.A.Q.

What's the difference between excess return and alpha?

Excess return is the simple difference between the fund’s return and the benchmark’s. Alpha adjusts for how much market risk the fund took, usually with a regression of the fund’s returns on the benchmark’s. Excess return is the right first check; alpha needs a return series, not just trailing returns.

Do these returns include dividends and fees?

They’re calculated from adjusted prices, so they include reinvested dividends and capital gains distributions, and they’re net of the fund’s expense ratio. They don’t deduct sales charges or taxes.

Can I compare a mutual fund with an ETF?

Yes. Mutual funds and ETFs are in the same returns table, keyed by ticker, so a fund-versus-ETF comparison is the same query. ETF returns are based on market price; mutual fund returns on NAV.

How do I rank every fund against a benchmark?

Drop the ticker filter in the fund CTE and sort by the excess return. Filter to funds with a non-null 10-year return so younger funds don’t distort the ranking.

To compare any US fund or ETF with its benchmark, start a free 30-day trial of the Fund Returns listing on the Snowflake Marketplace. It has 1-day to 20-year trailing returns and calendar-year returns for about 32,000 US mutual funds and ETFs. The schema is in the dataset docs. For where else to get fund returns, see Where to Get Trailing Returns for Every US Fund and ETF in Bulk. Questions? Reach out at contact@fruitstand.dev.

Home

Datasets

Company

How to Compare a Fund's Returns With Its Benchmark in SQL

The query

Real results

Picking the right benchmark

Comparing across calendar years

F.A.Q.

​The query

​Real results

​Picking the right benchmark

​Comparing across calendar years

​F.A.Q.

The query

Real results

Picking the right benchmark

Comparing across calendar years

F.A.Q.