Backtest a trading strategy

1. Pick one block per day

You cannot ask the price feed for “the price on 3 July”. You can only ask what it said at a given block. So first, turn each day into a block.

block_at returns the last block at or before a time. This step asks for the block at 00:00 UTC on each day from 1 July to 28 September 2026: 90 days.

SELECT
  day::DATE AS day,
  block_at($client, day) AS block_number
FROM generate_series(
  TIMESTAMP '2026-07-01',
  TIMESTAMP '2026-09-28',
  INTERVAL 1 DAY
) AS days(day)
ORDER BY day;

2. Read the price on each day

The ETH/USD feed lives at 0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419. read_contract calls two of its functions at each day’s block:

• latestRoundData returns the price in the answer field.
• decimals says how many digits of answer come after the decimal point. For ETH/USD it is 8, so an answer of 250000000000 means 2,500 USD.

The block number goes right after the function name. Keep answer as a whole number: the backtest needs exact values. Use format_units only to show the price.

WITH reads AS (
  SELECT
      day,
      block_number,
      read_contract(
          $client,
          '0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS,
          $feed_abi,
          'latestRoundData',
          block_number
      ) AS round,
      read_contract(
          $client,
          '0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS,
          $feed_abi,
          'decimals',
          block_number
      ) AS price_decimals
  FROM daily_blocks
)
SELECT
  day,
  block_number,
  (round).answer AS price,
  price_decimals,
  format_units((round).answer, price_decimals) AS eth_usd
FROM reads
ORDER BY day;

3. Write the buy and sell rules

A rule is a true or false value on each day:

• entry is true on days you want to buy.
• exit is true on days you want to sell.

This step makes two strategies:

• ma20 buys when the price is above the average of the last 20 days, and sells when it is below. ROWS 19 PRECEDING means “this day and the 19 days before it”. The rule waits until it has 20 days of prices.
• hold says buy on the first day and never says sell. It is the baseline to beat.

A rule may only look at today and earlier days. lag and PRECEDING are safe. lead, FOLLOWING and an average over all days (OVER ()) peek at future prices. A rule that peeks looks better in a backtest than it could ever do for real.

WITH averages AS (
  SELECT
      day,
      price,
      price_decimals,
      avg(price) OVER last_20_days AS average_20d,
      count(*) OVER last_20_days AS days_seen
  FROM daily_prices
  WINDOW last_20_days AS (ORDER BY day ROWS 19 PRECEDING)
)
SELECT
  'ma20' AS strategy,
  day,
  price,
  price_decimals,
  format_units(price, price_decimals) AS eth_usd,
  format_units(average_20d, price_decimals) AS average_20d_usd,
  days_seen = 20 AND price > average_20d AS entry,
  days_seen = 20 AND price < average_20d AS exit
FROM averages

UNION ALL

SELECT
  'hold',
  day,
  price,
  price_decimals,
  format_units(price, price_decimals),
  NULL,
  day = DATE '2026-07-01',
  false
FROM daily_prices
ORDER BY strategy, day;

4. Run the backtest

backtest_from_signals takes a query with one row per strategy and day. It needs these columns:

A signal trades on the next day, at the next day’s price. You decide after you see today’s price, so you can only trade after that. This is why hold buys on 2 July, and why a signal on the last day never trades.

The feed prices ETH in US dollars. This guide counts 1 USD as 1 USDC.

SELECT *
FROM backtest_from_signals((
  SELECT
      strategy,
      day::TIMESTAMP AS ts,
      price,
      entry,
      exit,
      parse_units('1000', 6) AS init_cash,
      30 AS fee_bps,
      18 AS base_decimals,
      6 AS quote_decimals,
      price_decimals
  FROM signals
))
ORDER BY strategy, ts;

5. List the trades

The ledger has one row per strategy and day. Most days have no trade. A row with a trade has side set to buy or sell. The amounts are whole numbers in each token’s smallest unit, so format_units makes them readable.

SELECT
  strategy,
  ts::DATE AS day,
  side,
  format_units(price, price_decimals) AS eth_usd,
  format_units(fill_size, 18) AS eth_traded,
  format_units(fee, 6) AS fee_usdc,
  format_units(equity, 6) AS value_usdc
FROM ledger
WHERE side IS NOT NULL
ORDER BY strategy, ts;

6. Compare the results

equity is what a strategy is worth on that day: its cash plus its ETH at that day’s price. The value on the last day is the final result, and arg_max(equity, ts) picks it.

exact_change turns the start and end values into an exact percent return. Do not subtract the raw amounts yourself: they are UINT256, a type with no negative numbers. A loss would wrap around to a huge number. If you need the difference, cast both to INT256 first.

SELECT
  strategy,
  count(side) AS trades,
  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_percent
FROM ledger
GROUP BY strategy
ORDER BY strategy;