> 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/deribit-perpetual-funding.md).

# Deribit perpetual funding

This page extends the [Deribit Insights tutorial](https://insights.deribit.com/dev-hub/deribit-perpetual-funding-python/) (SQ-25, [cryptarbitrage](https://github.com/cryptarbitrage-code/deribit-perpetual-funding)) from a single-instrument funding chart into a **cross-exchange comparison**: how much does a long-perp position pay on Deribit vs Binance for the same USD-quoted BTC perpetual? Both Deribit's `BTC-PERPETUAL` and Binance Coin-M's `BTCUSD_PERP` are inverse perps quoted in USD; `api.funding_rate` exposes them under the same Koinju universal symbol `BTC-USD-PERP`.

Without `api.funding_rate` you'd hit each exchange's REST endpoint directly — Deribit `get_funding_rate_history` (hourly, paged backward), Binance Coin-M `/dapi/v1/fundingRate` (8-hourly, paged forward) — match their bounds, dedup, align timezones, then merge. The SQL drops it all into one query.

## Query

```sql
WITH
    '2025-06-01' AS start_date,
    '2026-05-01' AS end_date,
    monthly AS (
        SELECT
            formatDateTime(timestamp, '%Y-%m') AS month,
            exchange,
            sum(toFloat64(funding_rate)) * 100 AS funding_pct
        FROM api.funding_rate
        WHERE market = 'BTC-USD-PERP'
          AND exchange IN ('deribit', 'binance')
          AND timestamp >= toDateTime64(start_date, 9, 'UTC')
          AND timestamp < toDateTime64(end_date, 9, 'UTC')
        GROUP BY month, exchange
    )
SELECT
    month,
    sumIf(funding_pct, exchange = 'deribit') AS deribit_funding_pct,
    sumIf(funding_pct, exchange = 'binance') AS binance_funding_pct,
    sumIf(funding_pct, exchange = 'binance')
        - sumIf(funding_pct, exchange = 'deribit') AS spread_pct
FROM monthly
GROUP BY month
ORDER BY month
```

Functions used: [`formatDateTime`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#formatdatetime), [`toFloat64`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tofloat64), [`toDateTime64`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#todatetime64), [`sumIf` (`-If` combinator)](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-if)

Two SQL details worth flagging:

* **Same universal name across venues.** `WHERE market = 'BTC-USD-PERP'` matches both Deribit's hourly `BTC-PERPETUAL` events and Binance Coin-M's 8-hourly `BTCUSD_PERP` settlements via the `dictGet` lookup in `api.funding_rate`. The Python equivalent has to know each exchange's native symbol, which API path serves it, and the per-exchange settlement cadence.
* **`sumIf` is the cheapest pivot.** No subqueries, no self-joins — ClickHouse evaluates one filter pass per `sumIf` call and emits a wide row directly.

## Output (2025-06 → 2026-04, BTC-USD-PERP)

11 rows — one per month for the full window:

| month   | deribit\_pct | binance\_pct | spread\_pct |
| ------- | ------------ | ------------ | ----------- |
| 2025-06 | +0.129       | +0.302       | +0.172      |
| 2025-07 | +0.934       | +0.759       | −0.176      |
| 2025-08 | +0.731       | +0.587       | −0.144      |
| 2025-09 | +0.496       | +0.324       | −0.172      |
| 2025-10 | +0.803       | +0.472       | −0.331      |
| 2025-11 | +0.009       | +0.384       | +0.375      |
| 2025-12 | +0.283       | +0.382       | +0.100      |
| 2026-01 | +0.476       | +0.498       | +0.021      |
| 2026-02 | −0.111       | −0.086       | +0.025      |
| 2026-03 | +0.042       | +0.106       | +0.065      |
| 2026-04 | −0.004       | −0.027       | −0.022      |

A long held over the full 11-month window paid **+3.79 %** on Deribit vs **+3.70 %** on Binance — within \~9 bps of each other on a cumulative basis, but the month-over-month spread swings by up to ±0.4 %. October 2025 was the largest one-month dislocation: Deribit funding ran \~33 bps richer than Binance, a window where shorting Deribit and going long Binance Coin-M would have collected the spread (before fees).

## Extending it

* **More exchanges** — append to the `IN (...)` list and add another `sumIf`. Same shape works for `bybit` and `okx` once their `BTC-USD-PERP` mappings are wired in (`reference_data.future_markets`).
* **8-hour rolling rate** — Deribit's UI reports `interest_8h` (rolling sum of the last 8 hourly values). Replace the `monthly` CTE with `sum(funding_rate) OVER (PARTITION BY exchange, market ORDER BY timestamp ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS rate_8h`.
* **Realised funding cost on a position** — multiply each `*_funding_pct` by your average position notional in the same `SELECT`. The article's GUI does this client-side; SQL does it inline.