> ## 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 Backtest a Trading Strategy in Snowflake SQL

> Backtest a 50/200-day moving-average crossover in Snowflake SQL with window functions: signals, T-bill cash, drawdowns and 33 years of SPY and QQQ results.

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

To backtest a trading strategy in Snowflake, compute the signal with window
functions over daily prices, shift it one day so each trade uses only past
data, then compound the daily returns into an equity curve. On SPY since
1993, a 50/200-day moving-average crossover returned 10.3% a year against
10.7% for buy-and-hold, with a maximum drawdown of 34% instead of 55%.

Below is the full SQL, the results for SPY and QQQ, the trades it made, and
the mistakes that make backtests look better than they are.

> [!NOTE]
>
> Prices come from Fruit Stand's Historical Stock Prices listing and T-bill
> rates from our FRED listing, through November 6, 2026. Every query here
> was run on November 8, 2026. A backtest describes the past, and this isn't
> investment advice.

## The strategy

The 50/200-day crossover is the classic trend rule, often called the golden
cross and the death cross:

* **Signal:** in the market when the 50-day simple moving average of the
  price is above the 200-day average, out when it's below.
* **Prices:** `adjusted_close`, which includes splits and dividends, so the
  returns are total returns. See [Adjusted Close vs.
  Close](https://fruitstand.dev/blog/adjusted-close-vs-close.md) for why the raw close breaks here.
* **Timing:** the signal is calculated at a day's close and acted on the
  next day, so the strategy never trades on information it didn't have yet.
* **Cash:** when out of the market, the money earns the 3-month Treasury
  bill rate (FRED series `DTB3`).
* **Test:** SPY (S\&P 500) and QQQ (Nasdaq-100), each from its 201st trading
  day, when the 200-day average first exists.

## Step 1: Build the daily positions

One query computes both averages, the position, and each day's strategy
return. Saving it as a table keeps the next queries short:

```sql theme={null}
create or replace temporary table backtest as
with prices as (
    select code, date,
           adjusted_close / lag(adjusted_close) over (partition by code order by date) - 1 as daily_return,
           avg(adjusted_close) over (partition by code order by date
               rows between 49 preceding and current row) as sma_50,
           avg(adjusted_close) over (partition by code order by date
               rows between 199 preceding and current row) as sma_200,
           row_number() over (partition by code order by date) as day_number
    from security_price_history_vw                       -- Historical Stock Prices
    where exchange_code = 'US' and code in ('SPY', 'QQQ')
),
tbills as (
    select date, value as tbill_rate
    from fred.fred_series_observations_vw                -- FRED Series
    where series_id = 'DTB3' and value is not null
),
positions as (
    select p.code, p.date, p.daily_return, p.day_number,
           -- act on yesterday's signal, so no day uses information it couldn't have had
           lag(iff(p.sma_50 > p.sma_200, 1, 0)) over (partition by p.code order by p.date) as in_market,
           lag(t.tbill_rate) over (partition by p.code order by p.date) as tbill_rate
    from prices p
    asof join tbills t match_condition (p.date >= t.date)
)
select code, date, daily_return, in_market,
       in_market * daily_return
           + (1 - in_market) * (power(1 + tbill_rate / 100, 1 / 252) - 1) as strategy_return,
       iff(in_market <> lag(in_market) over (partition by code order by date), 1, 0) as is_trade
from positions
where day_number > 200;
```

The two listings are separate databases in your account, so qualify each
view with the database name you chose when you got the listing.

Three details do most of the work:

* **`rows between 199 preceding and current row`** makes the window a fixed
  200 trading days. Skipping the first 200 days (`day_number > 200`) drops
  the rows where the average covers fewer days.
* **`lag(...)` on the signal** is what prevents look-ahead bias: today's
  return is earned by yesterday's decision.
* **`asof join`** matches each trading day to the latest T-bill rate on or
  before it, so holidays and missing rate days don't drop rows.

## Step 2: Score it against buy-and-hold

Compound the daily returns with `exp(sum(ln(1 + r)))`, track each curve's
running peak for drawdowns, and annualize:

```sql theme={null}
with curves as (
    select *,
           exp(sum(ln(1 + daily_return)) over (partition by code order by date)) as buy_hold,
           exp(sum(ln(1 + strategy_return)) over (partition by code order by date)) as strategy
    from backtest
),
drawdowns as (
    select *,
           buy_hold / max(buy_hold) over (partition by code order by date) - 1 as buy_hold_dd,
           strategy / max(strategy) over (partition by code order by date) - 1 as strategy_dd
    from curves
)
select code,
       min(date) as start_date,
       max(date) as end_date,
       round(power(max_by(buy_hold, date), 365.25 / datediff(day, min(date), max(date))) - 1, 4) as buy_hold_cagr,
       round(power(max_by(strategy, date), 365.25 / datediff(day, min(date), max(date))) - 1, 4) as strategy_cagr,
       round(min(buy_hold_dd), 3) as buy_hold_max_drawdown,
       round(min(strategy_dd), 3) as strategy_max_drawdown,
       round(stddev(daily_return) * sqrt(252), 3) as buy_hold_volatility,
       round(stddev(strategy_return) * sqrt(252), 3) as strategy_volatility,
       round(avg(in_market), 2) as time_in_market,
       sum(is_trade) as trades
from drawdowns
group by code
order by code;
```

## The results

Through November 6, 2026:

| | SPY buy-and-hold | SPY crossover | QQQ buy-and-hold | QQQ crossover |
| - | -: | -: | -: | -: |
| Start | Nov 12, 1993 | Nov 12, 1993 | Dec 22, 1999 | Dec 22, 1999 |
| Annual return | 10.68% | 10.27% | 8.38% | 11.52% |
| Maximum drawdown | −55.2% | −33.7% | −83.0% | −36.5% |
| Volatility (annualized) | 18.7% | 13.8% | 26.7% | 18.8% |
| Time in the market | 100% | 77% | 100% | 74% |
| Trades | — | 30 | — | 32 |

On SPY, the crossover gave up 0.4 points a year of return for a much
smoother ride: it was out for most of the 2008 crash, and its worst loss was
a third rather than more than half. $10,000 grew to $251,169 with the
strategy and \$284,394 buying and holding.

On QQQ, it beat buy-and-hold outright, but mostly because of when the test
starts. QQQ's 200th trading day fell in December 1999, three months before
the dot-com peak, and buy-and-hold then lost 83%. A backtest that starts at
a bubble's top flatters any rule that sidesteps the crash.

**The last 10 years tell a different story.** From November 7, 2016, in a
mostly rising market:

| | Buy-and-hold | Crossover |
| - | -: | -: |
| SPY | 15.24% a year | 11.34% a year |
| QQQ | 20.00% a year | 18.88% a year |

A trend rule pays for crash protection by being late, and in a decade with
fast recoveries it was late in both directions.

## The trades it made

```sql theme={null}
select date, iff(in_market = 1, 'Buy', 'Sell') as action
from backtest
where code = 'SPY' and is_trade = 1 and date >= '2018-01-01'
order by date;
```

| Date | Action |
| - | - |
| Dec 13, 2018 | Sell |
| Mar 27, 2019 | Buy |
| Apr 1, 2020 | Sell |
| Jul 7, 2020 | Buy |
| Mar 17, 2022 | Sell |
| Jan 27, 2023 | Buy |
| Apr 17, 2025 | Sell |
| Jun 30, 2025 | Buy |

The 2020 trades show the weakness. The S\&P 500 bottomed on March 23, 2020,
but the 50-day average only crossed below the 200-day on April 1, so the
strategy sold after the crash and bought back in July, higher. 2022 went
better: it sold in March and missed most of that year's decline.

## Mistakes that inflate a backtest

* **Look-ahead bias.** Trading on the same day's signal, which uses that
  day's close, lifts SPY's result from 9.79% to 10.15% a year before cash
  interest. Always `lag` the signal.
* **Ignoring cash.** Leaving cash at 0% understates a strategy that's out of
  the market a quarter of the time. T-bill interest added about half a point
  a year: SPY's crossover returned 9.79% without it and 10.27% with it.
* **Raw prices.** The raw `close` drops on every split and every dividend,
  so moving averages built on it trigger false sell signals. Use
  `adjusted_close`.
* **Costs.** At 0.1% per trade, 30 trades cost SPY's crossover about 0.1
  points a year (10.27% to 10.17%). Rules that trade weekly pay far more.
* **The start date.** As QQQ shows, the first day of the test can decide
  the answer. Rerun from a few different starts before trusting a result.
* **Survivorship bias.** Index ETFs like SPY and QQQ avoid it, but a stock
  backtest on today's companies doesn't include the ones that failed. The
  Historical Stock Prices listing doesn't include delisted securities.
* **Overfitting.** Testing many average lengths and keeping the best is
  easy in SQL, which makes it easy to fool yourself. Pick the rule first,
  or keep a period you don't look at until the end.

## Testing other tickers and rules

Change the `code in (...)` list to test any US stock, ETF or mutual fund in
the listing; the queries handle many tickers at once because every window is
`partition by code`. To test a different rule, change the `iff(...)` in the
`positions` step: for example, `iff(adjusted_close > sma_200, 1, 0)` for the
simpler price-above-200-day rule (add `adjusted_close` to the `prices`
step's columns first).

For more SQL on the same data, see [How to Calculate Inflation-Adjusted
Returns in SQL](https://fruitstand.dev/blog/inflation-adjusted-returns-sql.md), which joins the same
two listings.

## F.A.Q.

> [accordion] Can Snowflake run a backtest without Python?
>
> Yes. A rule built from moving averages, price levels or other window calculations is plain SQL, as above. Python in Snowflake (Snowpark) helps for strategies with complex state, such as stop-losses that depend on the entry price.

> [accordion] Does the golden cross beat buy-and-hold?
>
> Not on SPY since 1993: 10.27% a year against 10.68%, though with a 34% maximum drawdown instead of 55%. On QQQ from December 1999 it did beat buy-and-hold, largely because the test started just before the dot-com crash.

> [accordion] How do I avoid look-ahead bias in SQL?
>
> Calculate the signal from data up to a day's close, then use `lag()` so the position applies from the next day's return. Never let a row's own return decide its position.

> [accordion] Why use adjusted prices for a backtest?
>
> Adjusted prices include splits and dividends, so returns are total returns and moving averages don't jump on split days. The raw close would trigger false signals and leave out dividends.

> [accordion] How much compute does this use?
>
> Very little. The backtest above covers about 15,000 rows, and window functions over the full price history of thousands of tickers run in seconds on a small warehouse.

To run this on any US stock, ETF or mutual fund, start a free 30-day trial
of the [Historical Stock Prices listing on the Snowflake
Marketplace](https://app.snowflake.com/marketplace/listing/GZTYZTG3LNFA). The
T-bill rates come from the [FRED listing](https://app.snowflake.com/marketplace/listing/GZTYZ40XYNE).
The schema is in the [dataset docs](https://fruitstand.dev/datasets/historical-stock-prices.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.