# 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
```

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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.koinju.io/compute-engine/spot-futures-perp-spreads.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.