Spot / Futures / Perp Spreads

For each derivative BTC contract, the daily-close price spread against the binance/BTC-USDT spot reference. The same chart you'd build with cryptopandas + plotly to compare spot, perpetuals and quarterly futures side by side — except the data shaping (fetch + align + diff) lives entirely in one CTE chain.

Query

WITH
    '2024-12-01' AS start_date,
    '2024-12-31' AS end_date,
    candles AS (
        SELECT toDate(start) AS day, exchange, market, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE (
                  (exchange = 'binance' AND market = 'BTC-USDT')
               OR (exchange = 'binance-usdm-future' AND market IN ('BTC-USDT', 'BTC-USDT-2025-03-28'))
               OR (exchange = 'binance-coinm-future' AND market = 'BTC-USD-PERP')
              )
          AND start BETWEEN start_date AND end_date
    ),
    spot AS (
        SELECT day, close AS spot_close
        FROM candles
        WHERE exchange = 'binance' AND market = 'BTC-USDT'
    )
SELECT c.day, c.exchange, c.market,
       c.close, s.spot_close,
       c.close - s.spot_close AS spread,
       (c.close - s.spot_close) / s.spot_close * 100 AS spread_pct
FROM candles c
JOIN spot s ON c.day = s.day
WHERE NOT (c.exchange = 'binance' AND c.market = 'BTC-USDT')
ORDER BY c.day, c.exchange, c.market

Functions used: toDate, toFloat64

Two SQL details worth flagging:

• One candles CTE for everything. The basket lives in a single WHERE block — spot + perpetuals + quarterly future come back in one fetch. The spot CTE is a window on top of candles that we JOIN against to get the per-day reference price. Adding more contracts is one more OR clause; no extra fetch.

Output (December 2024)

93 rows = 31 days × 3 derivative contracts. First few rows:

day	exchange	market	close	spot_close	spread	spread_pct
2024-12-01	binance-coinm-future	BTC-USD-PERP	97326.1	97185.2	140.92	+0.15
2024-12-01	binance-usdm-future	BTC-USDT	97265.1	97185.2	79.92	+0.08
2024-12-01	binance-usdm-future	BTC-USDT-2025-03-28	102270.8	97185.2	5085.62	+5.23
2024-12-02	binance-coinm-future	BTC-USD-PERP	95920.0	95840.6	79.38	+0.08
2024-12-02	binance-usdm-future	BTC-USDT	95890.5	95840.6	49.88	+0.05
2024-12-02	binance-usdm-future	BTC-USDT-2025-03-28	100588.1	95840.6	4747.48	+4.95
2024-12-04	binance-coinm-future	BTC-USD-PERP	98773.9	98587.3	186.58	+0.19

Mean / min / max per contract for the month:

contract	mean spread	min spread	max spread	mean %	min %	max %
binance-coinm-future / BTC-USD-PERP	−10.52	−219.00	+186.58	−0.013	−0.234	+0.189
binance-usdm-future / BTC-USDT	−2.22	−59.14	+90.41	−0.002	−0.056	+0.089
binance-usdm-future / BTC-USDT-2025-03-28	+4007.58	+2940.50	+5094.11	+4.070	+3.070	+5.233

Two stories on one chart:

• Perpetuals trade tight. Both perp legs hover around zero (mean ±0.01 %, min/max within ±0.25 %) — funding payments do exactly what they're supposed to do, mean-reverting the perp price to spot.

• The quarterly future is in steep contango. A ~5 % premium for ~4 months to expiry annualises to roughly +15 % — the futures market was pricing in continued bull-run while spot was at the start of its end-of-2024 rally. You can read the carry trade directly off this column.

Extending it

• More contracts — append to the WHERE block in candles. Per added contract you get one more time series in the long-form output.

• Annualised basis — for the dated future, multiply spread_pct by 365 / days_to_expiry to get a comparable yield. See Cash-and-carry yield (companion future-vs-spot doc, when shipped) for the full pattern.

• Different base asset — change BTC references to ETH, SOL, etc. The spot/perp/future structure is the same.

• Alternate spot reference — change the spot CTE filter (e.g. coinbase / BTC-USD for an exchange-agnostic reference). Or compute multiple references and JOIN on both for a triangular view.