How to backtest a trading strategy with StockQL

StockQL is a market-data database, not a proprietary backtesting language. That is useful: the prices live in PostgreSQL, the strategy is ordinary SQL, and every assumption remains visible.

This guide builds a deliberately simple end-of-day strategy on SPY.US. It runs entirely inside PostgreSQL and creates only a session-local temporary view. You can inspect every daily return, send the result to Python or R, and replace the rule without moving the underlying history into another platform.

1. Define the strategy before reading the result

The test uses a 200-observation simple moving average as a trend filter:

• Hold SPY when its adjusted close is above its 200-trading-day average.
• Hold cash otherwise. Cash earns 0% in this example.
• Use today’s close to set the next trading day’s position. The one-row delay prevents look-ahead.
• Charge 5 basis points, or 0.05%, whenever the position changes.
• Use adjusted closes so splits and distributions do not appear as trading returns.
• Use no leverage, shorting, tax model, or slippage beyond the fixed transaction cost.

These assumptions are modest enough to audit and specific enough to reproduce. They are not a claim that this is the best trend rule. They are the contract the query must implement.

2. Connect and check the history

Open psql with the PostgreSQL connection string from your StockQL installation:

psql "$DATABASE_URL"

Before calculating a return, verify that the requested series exists and that its adjusted closes are usable:

SELECT
  COUNT(*) AS rows,
  MIN(date) AS first_date,
  MAX(date) AS last_date,
  COUNT(*) FILTER (
    WHERE adjusted_close IS NULL OR adjusted_close <= 0
  ) AS unusable_adjusted_closes
FROM stock_prices
WHERE exchange_code = 'US'
  AND symbol = 'SPY';

On the live StockQL snapshot used for this article, the query returned:

Your last date will move forward as StockQL ingests new sessions. A missing or non-positive adjusted close is a reason to stop and inspect the series, not something to fill silently inside the backtest.

3. Build the daily backtest

The following statement ran unchanged against the live database. It creates a temporary view, so it lasts only for the current PostgreSQL session.

CREATE OR REPLACE TEMP VIEW spy_sma_backtest AS
WITH features AS (
  SELECT
    date,
    adjusted_close::double precision AS adjusted_close,
    AVG(adjusted_close::double precision) OVER (
      ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW
    ) AS sma_200,
    COUNT(*) OVER (
      ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW
    ) AS observations
  FROM stock_prices
  WHERE exchange_code = 'US'
    AND symbol = 'SPY'
    AND adjusted_close > 0
),
signals AS (
  SELECT
    date,
    adjusted_close,
    sma_200,
    CASE WHEN adjusted_close > sma_200 THEN 1.0 ELSE 0.0 END AS signal
  FROM features
  WHERE observations = 200
),
positions AS (
  SELECT
    *,
    LAG(signal) OVER (ORDER BY date) AS position,
    LAG(adjusted_close) OVER (ORDER BY date) AS prior_close
  FROM signals
),
returns AS (
  SELECT
    *,
    adjusted_close / prior_close - 1.0 AS buy_hold_return,
    ABS(position - LAG(position, 1, 0.0) OVER (ORDER BY date)) AS turnover
  FROM positions
  WHERE position IS NOT NULL
)
SELECT
  date,
  adjusted_close,
  sma_200,
  position,
  buy_hold_return,
  position * buy_hold_return - 0.0005 * turnover AS strategy_return,
  turnover
FROM returns;

Each common table expression has one job:

features

Calculates the moving average and counts actual trading observations. It does not confuse 200 rows with 200 calendar days.

signals

Waits for a complete 200-observation window, then emits a long-or-cash decision.

positions

Shifts that decision by one row. A close can inform the following return; it cannot trade retroactively.

returns

Calculates adjusted close-to-close returns and detects each entry or exit.

The final expression subtracts 0.0005 for every unit of turnover. Because the position is either 0 or 1, each entry and each exit costs 5 basis points.

4. Measure return, volatility, and drawdown

A final balance alone is not enough. This query compounds the daily series, annualizes its observed volatility, measures drawdown from the running peak, counts position changes, and keeps buy-and-hold as a control:

WITH curve AS (
  SELECT
    *,
    EXP(SUM(LN(1.0 + strategy_return)) OVER (ORDER BY date)) AS strategy_equity,
    EXP(SUM(LN(1.0 + buy_hold_return)) OVER (ORDER BY date)) AS buy_hold_equity
  FROM spy_sma_backtest
),
with_drawdown AS (
  SELECT
    *,
    strategy_equity
      / MAX(GREATEST(strategy_equity, 1.0)) OVER (ORDER BY date) - 1.0
      AS strategy_drawdown
  FROM curve
),
stats AS (
  SELECT
    MIN(date) AS start_date,
    MAX(date) AS end_date,
    COUNT(*) AS trading_days,
    STDDEV_SAMP(strategy_return) * SQRT(252.0) AS strategy_volatility,
    MIN(strategy_drawdown) AS strategy_max_drawdown,
    COUNT(*) FILTER (WHERE turnover > 0) AS position_changes
  FROM with_drawdown
),
ending AS (
  SELECT strategy_equity, buy_hold_equity
  FROM with_drawdown
  ORDER BY date DESC
  LIMIT 1
)
SELECT
  start_date,
  end_date,
  trading_days,
  ROUND(((strategy_equity - 1.0) * 100.0)::numeric, 2) AS strategy_total_return_pct,
  ROUND(((POWER(strategy_equity, 365.25 / NULLIF(end_date - start_date, 0)) - 1.0) * 100.0)::numeric, 2) AS strategy_cagr_pct,
  ROUND((strategy_volatility * 100.0)::numeric, 2) AS strategy_volatility_pct,
  ROUND((strategy_max_drawdown * 100.0)::numeric, 2) AS strategy_max_drawdown_pct,
  position_changes,
  ROUND(((buy_hold_equity - 1.0) * 100.0)::numeric, 2) AS buy_hold_total_return_pct
FROM stats CROSS JOIN ending;

The live run covered 2,508 test days after the moving-average warmup:

The strategy did not beat buy-and-hold on total return. It spent time in cash and reduced observed volatility, but the foregone upside was substantial. That is a useful result: a backtest should reject attractive stories as readily as it supports them.

On the machine used for verification, the temporary-view statement completed in 15 ms, the summary in 289 ms, and the annual query below in 218 ms. Those timings describe one installation, not a general benchmark.

Inspect the result year by year

SELECT
  EXTRACT(YEAR FROM date)::integer AS year,
  ROUND(((EXP(SUM(LN(1.0 + strategy_return))) - 1.0) * 100.0)::numeric, 2) AS strategy_return_pct,
  ROUND(((EXP(SUM(LN(1.0 + buy_hold_return))) - 1.0) * 100.0)::numeric, 2) AS buy_hold_return_pct,
  COUNT(*) FILTER (WHERE turnover > 0) AS position_changes
FROM spy_sma_backtest
GROUP BY 1
ORDER BY 1;

* 2016 begins on September 19 after the 200-observation warmup. 2026 ends on September 10, the snapshot date. The buy-and-hold control is shown without transaction costs.

5. Pull the daily curve into your own tools

The temporary view remains inspectable for the rest of the psql session. Export it without rewriting the strategy in an application layer:

\copy (SELECT date, adjusted_close, sma_200, position, buy_hold_return, strategy_return, turnover FROM spy_sma_backtest ORDER BY date) TO 'spy-sma-backtest.csv' CSV HEADER

The CSV is ready for a notebook, chart, independent calculation, or review. Keeping the daily rows is important: aggregate metrics cannot reveal whether one bad price, one missing interval, or one unrealistic fill produced the result.

Change one assumption at a time

• Change SPY and US to test another instrument and exchange.
• Change both window frames from 199 PRECEDING to 99 PRECEDING for a 100-observation average.
• Change 0.0005 to your own one-way cost estimate.
• Add a cash return series rather than assuming idle capital earns zero.
• Split the sample into development and holdout periods before comparing many rules.

What StockQL contributes

The SQL in this guide is intentionally ordinary. The hard part is the history beneath it: retained delistings, coherent adjusted prices, per-exchange calendars, repeat ingestion checks, and direct access to the rows when an output looks wrong.

That separation is the point. StockQL maintains the research database. PostgreSQL, Python, R, or your own agent can own the strategy.

See how StockQL handles the data →