backtest_from_signals table function

Example

Runs locally

-- Daily ETH closes in whole USDC. Long while the close is above its 3-day average.
WITH prices(day, close) AS (VALUES
  ('2026-01-01', 2000), ('2026-01-02', 2050), ('2026-01-03', 2120), ('2026-01-04', 2200),
  ('2026-01-05', 2150), ('2026-01-06', 2050), ('2026-01-07', 1980), ('2026-01-08', 2060),
  ('2026-01-09', 2180), ('2026-01-10', 2100)
),
signals AS (
  SELECT day::TIMESTAMP AS ts, close,
         close > avg(close) OVER (ORDER BY day ROWS 2 PRECEDING) AS above_ma3
  FROM prices
),
ledger AS (
  FROM backtest_from_signals((
    SELECT ts, close AS price, above_ma3 AS entry, NOT above_ma3 AS exit,
           parse_units('10000', 6) AS init_cash, 30 AS fee_bps,   -- 10,000 USDC, 0.30% per fill
           18 AS base_decimals, 6 AS quote_decimals, 0 AS price_decimals
    FROM signals
  ))
)
-- Signals fill at the next close; day 10's exit has no next day, so the position stays open
SELECT ts::DATE AS day, close, above_ma3, side,
       format_units(size, 18) AS eth, format_units(equity, 6) AS equity_usdc
FROM ledger JOIN signals USING (ts)
ORDER BY ts;

Enable JavaScript to run this sample in the notebook. The SQL preview above is still available for reference.

API reference

backtest_from_signals(TABLE) #

Takes a query of prices, signals and settings and returns one ledger row per input row.

Inputs

Name	Type	Use
`signals`	TABLE One row per strategy and time, with the price, the entry and exit signals, and the strategy's settings as columns.	required positional

Input relation columns

Name	Type	Default
`ts`	TIMESTAMP TIMESTAMP or TIMESTAMPTZ; one row per time within a strategy. Required
`price`	UINT256 Quote per whole base token, as a positive integer scaled by 10^price_decimals. Required
`entry`	BOOLEAN Buy with all cash at the next row when flat. NULL means false. Required
`exit`	BOOLEAN Sell the whole position at the next row when holding. NULL means false. Required
`init_cash`	UINT256 Starting cash in raw quote units; must be positive. Required

Columns match by name, in any order; unknown or repeated columns are errors. Every column except ts, price, entry and exit must stay the same within a strategy. price and init_cash take UINT256, INT256 or integer types; decimals and fee_bps take integer types.

Result columns

Name	Type
`strategy`	VARCHAR Strategy name; 'default' when omitted.
`ts`	TIMESTAMP Row time in UTC.
`price`	UINT256 Input price for this row.
`side`	VARCHAR 'buy', 'sell', or NULL when nothing filled.
`fill_size`	UINT256 Base units traded; 0 when nothing filled.

One ledger row per input row. Returns each row's fill, the cash, base size and equity after it, and the strategy's tokens and decimals. Amounts are raw UINT256 integers.

Guidance

Trading rules

Each strategy starts with init_cash and no position. On each row, the previous row's order fills at this row's price, then this row's signals set the next order. So a signal on the last row never fills.

An entry while holding, or an exit while flat, does nothing.
If entry and exit are both true on one row, nothing happens, even when holding. To let the exit win, set entry to false wherever exit is true.
A buy that cannot afford one raw base unit, or a sale worth less than one raw quote unit, does not fill and charges no fee. After a skipped sale the position stays open.
A position still open at the end stays open and is valued at the last price.

Signals and look-ahead bias

Fills wait for the next row, but that does not make the signals honest. A signal must use only data up to its own row.

Safe: lag(), and window frames that end at the current row, such as ROWS 2 PRECEDING.
Leaks future prices: lead(), frames with FOLLOWING, and averages over the whole series such as avg(price) OVER ().
Strategies share no cash or position. Run many in one call for a parameter sweep or a buy-and-hold benchmark.

Prices and exact amounts

Balances stay exact UINT256 integers. A buy spends at most floor(cash * 10000 / (10000 + fee_bps)), rounds its cost up, and rounds the fee up on what it spent. A sale rounds proceeds down and the fee up. A result too large for UINT256 raises an error.

Turn prices and cash into scaled integers first, for example with parse_units. DOUBLE and DECIMAL are rejected.
Pass the real token decimals; all three default to 18.
Price in the token you trade: an ETH/USD feed is not an ETH/USDC price.

Reading results

The output has one row per input row. Group by strategy to sum up each run.

Fills are the rows WHERE side IS NOT NULL; count(side) counts them.
Final equity is arg_max(equity, ts).
exact_change(init_cash, quote_decimals, final_equity, quote_decimals) gives the exact return.
UINT256 subtraction wraps around below zero. Cast to INT256 first: equity::INT256 - init_cash::INT256.
Output order is not guaranteed; add ORDER BY strategy, ts.

Runs locally

-- A momentum strategy against buy-and-hold, 1,000 USDC each
WITH prices(ts, price) AS (VALUES
  (TIMESTAMP '2026-01-01', 2000), (TIMESTAMP '2026-01-02', 2100),
  (TIMESTAMP '2026-01-03', 2200), (TIMESTAMP '2026-01-04', 2000)
),
signals AS (
  SELECT 'momentum' AS strategy, ts, price,
         price > lag(price) OVER (ORDER BY ts) AS entry,
         price < lag(price) OVER (ORDER BY ts) AS exit
  FROM prices
  UNION ALL
  SELECT 'hold', ts, price, ts = TIMESTAMP '2026-01-01', false
  FROM prices
),
ledger AS (
  FROM backtest_from_signals((
    SELECT *, parse_units('1000', 6) AS init_cash, 30 AS fee_bps,
           18 AS base_decimals, 6 AS quote_decimals, 0 AS price_decimals
    FROM signals
  ))
)
SELECT strategy, count(side) AS fills,
       format_units(sum(fee), 6) AS fees_usdc,
       format_units(arg_max(equity, ts), 6) AS final_usdc,
       (exact_change(parse_units('1000', 6), 6, arg_max(equity, ts), 6)).change_percent AS return_pct
FROM ledger
GROUP BY strategy
ORDER BY strategy;

Enable JavaScript to run this sample in the notebook. The SQL preview above is still available for reference.