> For the complete documentation index, see [llms.txt](https://docs.koinju.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.koinju.io/sql-cookbook/spot-futures-perp-spreads.md).

# 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

```sql
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`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#todate), [`toFloat64`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#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](https://gitlab.com/koinju/connector/exporter/-/blob/master/README.md) (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.