# Quick start

Koinju delivers institutional-grade crypto market data — tick trades (418 billion+ since 2013), OHLCV candles, funding rates, and recomputed option Greeks — across 13 exchanges under one normalized schema.

Two ways in: a **REST API** for simple pulls, and **direct SQL** (ClickHouse) for bulk analytics and server-side compute. See [Pricing](/pricing) for tiers.

Zero to a plotted BTC chart in three steps. Full connector reference (Rust, Go, CLI, BI tools, REST) is in [How to connect](/how-to-connect).

{% hint style="success" %}
**Using an AI coding agent?** Install the [Koinju agent skill](/ai-agent-skill) so Claude Code / Cursor / Copilot already know the schema, auth, and query patterns and can help you get started with Koinju API faster.

```bash
npx skills add https://gitlab.com/koinju-public/agent-skill
```

{% endhint %}

## 1. Get credentials

You need a database URL (host), username, and password. Sign up at [koinju.io/pricing](https://koinju.io/pricing) to get them, or email [support@koinju.io](mailto:support@koinju.io?subject=SQL%20API%20credentials%20request).

## 2. First SQL query (Python)

```
pip install clickhouse-connect pandas matplotlib
```

```python
import clickhouse_connect

conn = clickhouse_connect.get_client(
    host="<provided_database_url>",
    port=8443,
    secure=True,
    username="<username>",
    password="<password>",
    database="api",
)

df = conn.query_df(
    """
    SELECT start, exchange, market, toFloat64(close) AS close
    FROM api.ohlcv(candle_duration_in_minutes = 1440)
    WHERE market IN ('BTC-USD', 'BTC-USDT')
      AND start >= now() - INTERVAL 90 DAY
    ORDER BY start
    """
)
print(df.head())
```

`api.ohlcv(candle_duration_in_minutes = 1440)` is a parameterized view (`1440` = daily candles). `close` is a high-precision Decimal — cast it with `toFloat64(close)` before arithmetic.

{% hint style="info" %}
The `market` symbol is quoted per venue: USD venues (Coinbase, Kraken, Bitstamp) publish `BTC-USD`; USDT venues (Binance, OKX, Bybit, KuCoin, Gate.io) publish `BTC-USDT`. These are **different markets** — to see BTC across all exchanges filter `market IN ('BTC-USD', 'BTC-USDT')`.
{% endhint %}

## 3. First plot — average BTC price across all exchanges

```python
import matplotlib.pyplot as plt

avg = df.groupby("start")["close"].mean()

avg.plot()
plt.title("Average BTC price across all exchanges (daily close)")
plt.ylabel("USD")
plt.show()
```

Next: browse [Data](/data/coverage), or use the REST option in [How to connect](/how-to-connect).


# AI agent skill

Koinju ships an open **agent skill** that teaches AI coding assistants — [Claude Code](https://claude.com/claude-code), Cursor, GitHub Copilot, Windsurf, Gemini, and 15+ others — how to work with this API.

## Why use it

* Fast development environement setup for testing the API
* Auto-discoverability of API capabilities (endpoints, SQL tables, data types)
* Help with optimisation of SQL queries and API calls
* Help with integration of API data into research, backtesting, and production pipelines

It bundles:

* **Schema knowledge** — every `api.*` table and column
* **Auth + setup** — a one-command project bootstrap (`uv`/`venv`, dependencies, `.koinju.env`)
* **REST + SQL routing** — when to hit the public `/market/*` endpoints vs. ClickHouse SQL.
* **Cookbook recipes** — How to run common financial data analysis (Sortino, max drawdown, Bollinger, historical volatility, cross-exchange arbitrage, …) inside the koinju DB.
* **Integration patterns** — wiring Koinju into existing trading and research stacks.

## Install

Via the [skills.sh](https://skills.sh) CLI:

```bash
npx skills add https://gitlab.com/koinju-public/agent-skill        # into ./.claude/skills/
npx skills add https://gitlab.com/koinju-public/agent-skill -g     # global ~/.claude/skills/
```

Update later with `npx skills update koinju`. The skill is open source at [gitlab.com/koinju-public/agent-skill](https://gitlab.com/koinju-public/agent-skill).

## Example prompts it handles

Once installed, describe what you want in plain language — the agent picks the right interface (REST vs SQL), writes the query, handles credentials, and returns a working result:

* "Set up a project to test the Koinju API."
* "What Bitcoin products are available on Koinju?"
* "Plot the BTC option smile for OKX and Bybit using Koinju data."
* "Retrieve the implied volatility for BTC options."
* "Optimize the query that computes the average price of LINK-USDT over the past year."
* "Benchmark this strategy against buy-and-hold using Koinju data."
* "We have a Postgres database of crypto prices — how do I integrate it with Koinju?"

{% hint style="info" %}
Don't have credentials yet? Sign up at [koinju.io/pricing](https://koinju.io/pricing). The skill walks the agent (and you) through the rest.
{% endhint %}


# Pricing

418 billion+ trades across 13 exchanges. REST API and direct SQL access on every tier — including Free. All tiers include spot, futures, and options data; the time window varies.

## Tiers

|                        | Free (0 EUR)    | Developer (39 EUR/mo) | Professional (99 EUR/mo) | Business (299 EUR/mo) | Enterprise (from 1000 EUR/mo) |
| ---------------------- | --------------- | --------------------- | ------------------------ | --------------------- | ----------------------------- |
| **OHLCV daily/hourly** | Full history    | Full history          | Full history             | Full history          | Full history                  |
| **OHLCV 1-min**        | Rolling 1 month | Rolling 1 year        | Full history             | Full history          | Full history                  |
| **Spot trades**        | Rolling 24h     | Rolling 90 days       | Rolling 1 year           | Complete              | Complete                      |
| **Futures data**       | Rolling 24h     | Rolling 90 days       | Rolling 1 year           | Complete              | Complete                      |
| **Options data**       | Rolling 24h     | Rolling 90 days       | Rolling 1 year           | Complete              | Complete                      |

## REST API Limits

|                       | Free | Developer | Professional | Business | Enterprise |
| --------------------- | ---- | --------- | ------------ | -------- | ---------- |
| **Items per request** | 100  | 1,000     | 1,000        | 1,000    | Custom     |
| **Requests per day**  | 100  | 100       | 1,000        | 10,000   | Custom     |

## SQL Limits

### Per-month quotas

|                    | Free   | Developer | Professional | Business   | Enterprise    |
| ------------------ | ------ | --------- | ------------ | ---------- | ------------- |
| **query\_selects** | 50     | 500       | 1,000        | 10,000     | 100,000       |
| **result\_rows**   | 50,000 | 500,000   | 5,000,000    | 50,000,000 | 1,000,000,000 |

`SHOW`, `DESCRIBE`, and `EXPLAIN` queries are exempt from the queries/month counter.

### Shared limits (all tiers)

| Type                | Limit                    |
| ------------------- | ------------------------ |
| **execution\_time** | 60 seconds max per query |
| **data\_transfer**  | 100 GB per month         |

{% hint style="info" %}
**After upgrading**, reconnect your SQL client so the new quotas and history window apply — see [How to connect](/how-to-connect). REST requests update automatically.
{% endhint %}

## Infrastructure & Enterprise

|                                     | Free | Developer | Professional | Business | Enterprise |
| ----------------------------------- | ---- | --------- | ------------ | -------- | ---------- |
| **Remote replication**              | —    | —         | —            | Eligible | Eligible   |
| **On-premise**                      | —    | —         | —            | —        | Eligible   |
| **Private link**                    | —    | —         | —            | Eligible | Eligible   |
| **Pricing methodology (valuation)** | —    | —         | —            | —        | Eligible   |

## Support

|           | Free      | Developer | Professional | Business | Enterprise        |
| --------- | --------- | --------- | ------------ | -------- | ----------------- |
| **Level** | Community | Email     | Priority     | Priority | Dedicated manager |

## Time-Window Clamping

A query that **partially** overlaps your tier's allowed time window returns only the data that falls within the window. For example, a Free-tier user querying 7 days of spot trades receives only the last 24 hours; a Developer querying 2 years of 1-minute OHLCV candles receives only the last year.

On the **REST API**, a request whose **entire** range is older than your tier's window returns **HTTP 422** with a JSON body — a plain-language `message`, a ready-to-run `example_url` inside your window, and a `discord_url` for help — instead of an empty response. (Live on `/ohlcv` and `/trade`.)

{% hint style="info" %}
If a query returns fewer rows than expected, the requested range likely exceeds your tier's data window. Upgrade your tier to access deeper history.
{% endhint %}

## REST vs SQL

REST and SQL serve different use cases. REST is designed for recent, small data pulls and app integrations. SQL is designed for historical analysis, backtesting, and bulk computation.

For example, downloading 1 year of 1-minute candles for one pair:

* **REST** at 1,000 items/req: 526 paginated requests
* **SQL**: 1 query, server-side, result in \~1.7 seconds

The REST API is the discovery and integration layer. For any workload exceeding a few thousand rows, use SQL.

## Enterprise

For workloads beyond Business tier limits, we offer custom Enterprise plans (starting from 1,000 EUR/mo) with dedicated resources, SLAs, remote replication, on-premise deployment, private link, and pricing methodology (valuation) eligibility. [Contact us](mailto:contact@koinju.io?subject=Enterprise%20plan%20inquiry) for details.


# How to connect

## SQL API

[ClickHouse](https://clickhouse.com/) is an open-source column-oriented DBMS for online analytical processing (OLAP) that allows users to generate analytical reports using SQL queries in real-time.

The database can be interacted with either via its CLI client or connector available for all the mainstream languages.

### Authentication

Sign up at [koinju.io/pricing](https://koinju.io/pricing) — or [Contact Koinju](mailto:support@koinju.io?subject=SQL%20API%20credentials%20request\&body=Hi%2C%20I%20would%20like%20to%20get%20an%20API%20key%20to%20access%20the%20Koinju%20Market%20Data%20REST%20API) — to get the database url and your credentials, the following examples show how to authenticate yourself with them.

{% hint style="info" %}
**Upgraded your plan?** Your tier's limits and data-history window are bound to a SQL connection when it opens. After upgrading, **reconnect** — close and reopen the connection, or restart your BI tool / connection pool — so the new tier takes effect. An already-open session does not pick up the change and may return `Not enough privileges` until it reconnects. (REST API requests pick up your new tier automatically — no action needed.)
{% endhint %}

## Python

Install `clickhouse-connect`

```
pip install clickhouse-connect
```

Because the connector allows to query directly into a pandas DataFrame, if this feature is desired `pandas` should be installed as well.

Get the last 20 trades for any instrument starting with `BTC`

```python
import clickhouse_connect

conn = clickhouse_connect.get_client(
    host="<provided_database_url>",
    port=8443,
    secure=True,
    username="<username>",
    password="<password>",
    database="api",
)
df = conn.query_df(
    "select * from trade where  market like 'BTC%' and timestamp > toStartOfDay(now()) order by timestamp desc limit 20"
)
print(df.columns)
print(df[["exchange", "market", "timestamp", "price", "quantity", "side"]].head())
```

Outputs

```log
Index(['exchange', 'market', 'side', 'quantity', 'price', 'timestamp',
       'hostname', 'trade_id', 'hash', 'ts_reception', 'fill_trade',
       'created_at'],
      dtype='object')
              exchange    market  ...                quantity  side
0              binance   BTCUSDT  ...  0.00005000000000000000   buy
1  binance-usdm-future   BTCUSDT  ...  0.04900000000000000000  sell
2             coinbase   BTC-USD  ...  0.00001337000000000000  sell
3              binance  BTCFDUSD  ...  0.01893000000000000000   buy
4              binance  BTCFDUSD  ...  0.00036000000000000000   buy

[5 rows x 6 columns]
```

The query includes an additional filter by timestamp to optimize speed. The `trade` table, over 40TB, contains all public trades across several exchanges. While the entire dataset is searchable, limiting the time frame ensures results return in milliseconds instead of seconds, especially if queries do not match existing indexes. More details on query optimization are provided for each endpoint and in a general overview.

{% hint style="info" %}
All datetimes returned by koinju timezone aware and set to UTC. By default the connector will convert them to the user's timezone. So care should be applied if for some reasons the timezone awareness need to be dropped ( as for example when storing results in a excel spreadsheet).
{% endhint %}

Type equivalence between clickhouse and python : <https://clickhouse.com/docs/integrations/python#read-format-options-python-types>

## Rust

Install [clickhouse-rs](https://github.com/suharev7/clickhouse-rs) and other dependecies

```sh
[dependencies]
clickhouse = { version = "0.13.3", features = ["rustls-tls", "time"] }
serde = { version = "1.0.219", features = ["derive"] }
tokio = { version = "1", features = ["full"] }
time = "0.3.41"
rust_decimal = { version = "1.37.1", features = ["serde-str"] }
```

Then execute the following

```rust
use clickhouse::sql::Identifier;
use clickhouse::Client;
use clickhouse::Row;
use rust_decimal::Decimal;
use serde::{Deserialize, Deserializer};
use std::str::FromStr;
use time::OffsetDateTime;

#[derive(Row, Deserialize, Debug)]
struct Trade {
    exchange: String,
    market: String,
    #[serde(with = "clickhouse::serde::time::datetime64::nanos")]
    timestamp: OffsetDateTime,
    price: Decimal,
    quantity: Decimal,
    side: String,
}

#[tokio::main]
async fn main() {
    let client = Client::default()
        // should include both protocol and port
        .with_url("<provided_database_url>:8443")
        .with_user("<username>")
        .with_password("<password>")
        .with_database("api");
    let  cursor = client
    .query("select exchange ,market ,timestamp ,toString(price),toString(quantity), side from api.trade where  market like 'BTC%' and timestamp > toStartOfDay(now()) order by timestamp desc limit 20")
    .fetch_all::<Trade>().await.unwrap();
    dbg!(&cursor);
}

```

Ouputs

```
[src/main.rs:31:5] &cursor = [
    Trade {
        exchange: "binance",
        market: "BTCUSDT",
        timestamp: 2025-06-09 14:18:17.242 +00:00:00,
        price: 107414.47,
        quantity: 0.001,
        side: "buy",
    },
    Trade {
        exchange: "binance",
        market: "BTCUSDT",
        timestamp: 2025-06-09 14:18:17.192 +00:00:00,
        price: 107414.48,
        quantity: 0.00009,
        side: "sell",
    },
....
```

The conversion of the Decimal type into String in the query allows to decode the strings directly into Decimal type from `rust_decimal` crate.

Type equivalence between clickhouse and rust [: ](https://clickhouse.com/docs/integrations/rust#data-types)<https://clickhouse.com/docs/integrations/rust#data-types>

## Golang

Install \[clickhouse-go]\(<https://github.com/ClickHouse/clickhouse-go>)

```
go get -u github.com/ClickHouse/clickhouse-go/v2
```

By default all the decimal numbers in our tables are represented in \[Decimal256 with a scale of 20]\(<https://clickhouse.com/docs/sql-reference/data-types/decimal>) as to not lose any precision from data recieved from the exchages.

If user value correctness the users can stick to decimal by installing and using this \[decimal library]\(<https://github.com/shopspring/decimal> ) ) or if speed is a priority simply using float. The connector will accept both values for field structs represented as decimals in the database.

```go
package main

import (
	"context"
	"crypto/tls"
	"github.com/ClickHouse/clickhouse-go/v2"
	"github.com/shopspring/decimal"
	"time"
)

func main() {
	ctx := context.Background()
	conn, err := clickhouse.Open(&clickhouse.Options{
		Addr: []string{"<provided_database_url>:9440"},
		Auth: clickhouse.Auth{
			Database: "api",
			Username: "<user>",
			Password: "<password>",
		},
		TLS: &tls.Config{},
		Compression: &clickhouse.Compression{
			Method: clickhouse.CompressionLZ4,
		},
	})
	if err != nil {
		panic(err)
	}
	var result []struct {
		Exchange  string          `ch:"exchange"`
		Market    string          `ch:"market"`
		Timestamp time.Time       `ch:"timestamp"`
		Price     decimal.Decimal `ch:"price"`
		Quantity  decimal.Decimal `ch:"quantity"`
		Side      string          `ch:"side"`
	}
	err = conn.Select(ctx, &result,
		"select exchange, market, timestamp, price, quantity , side from trade where  market like 'BTC%' and timestamp > toStartOfDay(now()) order by timestamp desc limit 20");
	if err != nil {
		panic(err)
	}
	for _, row := range result[:5] {
		println(row.Exchange, row.Market, row.Timestamp.String(), row.Price.String(), row.Quantity.String(), row.Side)
	}

}
```

Outputs

```
binance BTCUSDT 2025-06-07 15:41:19.39 +0000 UTC 105465 0.00044 buy
binance-usdm-future BTCUSDT 2025-06-07 15:41:19.365 +0000 UTC 105400.6 0.01 buy
binance-usdm-future BTCUSDT 2025-06-07 15:41:19.335 +0000 UTC 105400.6 0.008 buy
binance BTCUSDT 2025-06-07 15:41:19.282 +0000 UTC 105465 0.001 buy
binance BTCUSDT 2025-06-07 15:41:19.214 +0000 UTC 105465.01 0.001 sell
```

Type equivalence between clickhouse and go: <https://clickhouse.com/docs/integrations/go#type-conversions>

### Other methods

Using ClickHouse CLI Client\
doc : <https://clickhouse.com/docs/integrations/sql-clients/cli>\
\
Using BI and visualization tools\
doc : <https://clickhouse.com/docs/integrations/data-visualization>\
\
Using programming language clients and various third-party services\
doc : <https://clickhouse.com/docs/integrations>\
\
**Data formats :**\
\
Along with getting the SQL result directly, ClickHouse also supports exporting data in various formats, like CSV, parquet etc.\
doc : <https://clickhouse.com/docs/integrations/data-formats>

## REST API

The REST API uses a two-tier access model:

| Endpoints          | Authentication               | Free                   | Developer             | Professional         | Business              | Enterprise |
| ------------------ | ---------------------------- | ---------------------- | --------------------- | -------------------- | --------------------- | ---------- |
| `/market/*`        | None (public)                | 10 req/s               | 10 req/s              | 10 req/s             | 10 req/s              | Custom     |
| `/ohlcv`, `/trade` | API key (`x-api-key` header) | 100 req/day, 100 items | 100 req/day, 1K items | 1K req/day, 1K items | 10K req/day, 1K items | Custom     |

See [Pricing](/pricing) for full tier details.

### Public Endpoints

Market listing endpoints are open — no authentication required.

#### curl

{% code overflow="wrap" %}

```bash
curl -s 'https://api.koinju.io/market/spot'
```

{% endcode %}

#### python

```python
import requests

response = requests.get('https://api.koinju.io/market/spot')
```

### Private Endpoints

The `/ohlcv` and `/trade` endpoints require an API key passed in the `x-api-key` header.

Sign up at [koinju.io/pricing](https://koinju.io/pricing) — or [Contact Koinju](mailto:contact@koinju.io?subject=Rest%20API%20key%20request\&body=Hi%2C%20I%20would%20like%20to%20get%20an%20API%20key%20to%20access%20the%20Koinju%20Market%20Data%20REST%20API) — to get your API key.

#### curl

{% code overflow="wrap" %}

```bash
curl -s -H 'x-api-key: YOUR_API_KEY' \
  'https://api.koinju.io/ohlcv?exchange=deribit&market=BTC-PERPETUAL&candle_duration_in_minutes=60&start_datetime=2026-03-01T00:00:00Z&end_datetime=2026-03-01T12:00:00Z'
```

{% endcode %}

#### python

```python
import requests

headers = {'x-api-key': 'YOUR_API_KEY'}
response = requests.get(
    'https://api.koinju.io/ohlcv',
    headers=headers,
    params={
        'exchange': 'deribit',
        'market': 'BTC-PERPETUAL',
        'candle_duration_in_minutes': 60,
        'start_datetime': '2026-03-01T00:00:00Z',
        'end_datetime': '2026-03-01T12:00:00Z',
    },
)
```

Part of the documentation is generated from OpenAPI, the spec file is [available here](https://openapi.gitbook.com/o/dUnaFW0Jh2YT6mdGcdaQ/spec/rest-market-data-api.yaml).


# SQL API Specifics

For SQL query limits and quotas per tier, see [Pricing](/pricing).

## Tables and schema discovery

While this documentation provides the tables' description it can always be generated by querying the table.

### List all available tables

```sql
SHOW tables from api
```

*Example Output:*

```
┌─name──────────────────────┐
│ market_future             │
│ market_option             │
│ market_spot               │
│ ohlcv                     │
│ trade                     │
└───────────────────────────┘

```

### Describe a table

```sql
DESCRIBE api.market_future
```

*Example Output:*

```
+------------------------+-----------------------+
|name                    |type                   |
+------------------------+-----------------------+
|exchange                |LowCardinality(String) |
|market_symbol           |Nullable(String)       |
|underlying_asset        |String                 |
|quote_asset             |String                 |
|settling_asset          |String                 |
|denomination_asset      |String                 |
|expiration              |Nullable(DateTime64(9))|
|contract_size           |Decimal(38, 18)        |
|contract_type           |String                 |
|future_type             |String                 |
|exchange_specific_symbol|String                 |
+------------------------+-----------------------+

```


# Private Link Setup

For advanced clients wishing consuming data from services hosted on AWS we provide the possibility to connect to our database through AWS Private Link.

## 1) Request the Service name and DNS name from Koinju

## 2) Create AWS Endpoint

**AWS console**

Open the AWS console and Go to **VPC** → **Endpoints** → **Create endpoints**.

Select **Endpoint services that use NLBs and GWLBs** and use `Service name` or `endpointServiceId` you got from [Obtain Endpoint "Service name" ](https://clickhouse.com/docs/manage/security/aws-privatelink#obtain-endpoint-service-info)step in **Service Name** field. Click **Verify service**:

<figure><img src="/files/kkohfkGRi5UFzfsCiKVE" alt=""><figcaption></figcaption></figure>

If you want to establish a cross-regional connection via PrivateLink, enable the "Cross region endpoint" checkbox and specify the service region as `eu-central-1`.

If you get a "Service name could not be verified." error, please contact Koinju team.

Next, select your VPC and subnets:

<figure><img src="/files/Wyuy4gEqZIaHhxYRTFfz" alt=""><figcaption></figcaption></figure>

> Make sure that `Enable DNS Name` is selected

As an optional step, assign Security groups/Tags:

> Make sure that ports `443`, `8443`, `9440`, `3306` are allowed in the security group.

After creating the VPC Endpoint, communicate the `Endpoint ID` to the Koinju team that will connect it to the clickhouse instance.\
![](/files/dPWh7HBos7GFMmms0wCZ)

## 3) Test Connection

Once green-lit by the Koinju team you will be able to connect to the clickhouse instance via the DNS name provided to you in step 1 from within your VPC.

> More information about the private link setup : <https://clickhouse.com/docs/manage/security/aws-privatelink>


# REST API Specifics

The rest API is documented via Open API spec which, with facilities to generate SDK code is available here <https://gitlab.com/koinju-public/market-data-sdk>


# Data types

## Decimals

All decimal data is represented as 256 bits decimals with 76 digits precision and 20 decimal digits.\
Because this precision is higher than most tokens this allows us to transmit to user the exact numbers as received from the exchange without any rounding.

Depending on the users need ( speed vs precision ) they can convert them to float either on the server side in the query on directly in their code once the data was received.

More information about the clickhouse decimal types : <https://clickhouse.com/docs/sql-reference/data-types/decimal>


# Coverage

Following is the list of supported exchanges with its id (which needs to be used for all data requests) and the historical date from which the data is available and validated.

| Exch Name                | Exch ID              | Historical Date |
| ------------------------ | -------------------- | --------------- |
| 1. Binance               | binance              | 2017-08-01      |
| 2. Binance Coin-M Future | binance-coinm-future | 2020-08-01      |
| 3. Binance USD-M Future  | binance-usdm-future  | 2019-09-01      |
| 4. Bitfinex              | bitfinex             | 2013-01-14      |
| 5. Bitfinex Future       | bitfinex-future      | 2019-07-03      |
| 6. Bitstamp              | bitstamp             | 2024-08-01      |
| 7. Bybit                 | bybit                | 2022-11-10      |
| 8. Bybit Coin-M Future   | bybit-coinm-future   | 2019-10-01      |
| 9. Bybit USD-M Future    | bybit-usdm-future    | 2020-03-25      |
| 10. Coinbase             | coinbase             | 2014-12-01      |
| 14. Crypto.com           | cryptodot-com        | 2023-10-01      |
| 15. Crypto.com Future    | cryptodot-com-future | 2024-11-01      |
| 16. Deribit              | deribit              | 2023-04-24      |
| 17. Deribit Future       | deribit-future       | 2017-01-06      |
| 18. Deribit Option       | deribit-option       | 2016-11-29      |
| 19. Gateio               | gateio               | 2021-01-01      |
| 20. Gemini               | gemini               | 2024-06-01      |
| 21. Kraken               | kraken               | 2013-10-01      |
| 22. Kraken Future        | kraken-future        | 2021-05-27      |
| 23. Kucoin               | kucoin               | 2022-12-31      |
| 24. Kucoin Future        | kucoin-future        | 2023-01-01      |
| 25. OKX                  | okx                  | 2021-10-01      |

The dates above are for trades and candles (OHLCV). The **option-chain** dataset (`api.option_chains` — 5-minute IV / greeks snapshots) is separate and more recent: available from **2026-05-12** across Deribit, Binance, OKX, and Bybit. For option history before that date, use the option **trade** tape (`deribit-option`, from 2016-11-29) and recompute IV / greeks from the premium.

#### Data delay

Currently the market data is delayed by 5 seconds. Arrangement can be made for real-time data on request from the client.


# Market list

## SQL API

Our ClickHouse SQL interface provides direct access to normalized market metadata through three optimized views: `market_spot`, `market_future`, and `market_option`. These tables enable sophisticated filtering and joins impossible via REST, empowering complex analytics workflows.

***

### `api.market_spot`

*Spot markets (e.g., BTC-USD, BTC-USDT)*

| Column                     | Type   | Description                                                                                                      |
| -------------------------- | ------ | ---------------------------------------------------------------------------------------------------------------- |
| `exchange`                 | String | Venue id — mostly clean spot names (e.g. `binance`, `coinbase`); run `SELECT DISTINCT exchange` for the full set |
| `market_symbol`            | String | Universal symbol, format `BASE-QUOTE` (e.g. `BTC-USDT` on Binance, `BTC-USD` on Coinbase)                        |
| `base_asset`               | String | Base asset, universal symbol (e.g. `BTC`)                                                                        |
| `quote_asset`              | String | Quote asset, universal symbol (e.g. `USDT`, `USD`)                                                               |
| `exchange_specific_symbol` | String | Raw exchange websocket ticker (e.g. `BTCUSDT`)                                                                   |

**Use Case Example**\
Find all spot markets with USDT quote pairs on Binance or Kraken:

```sql
SELECT exchange, market_symbol, base_asset, quote_asset  
FROM api.market_spot  
WHERE quote_asset = 'USDT'  
  AND exchange IN ('binance', 'kraken')  
```

List all exchanges:

```sql
SELECT distinct exchange FROM api.market_spot
```

***

### `api.market_future`

*Futures contracts (e.g., BTC-USD-PERP-INV inverse, BTC-USDT-PERP linear)*

| Column                     | Type           | Description                                                                                                                                          |
| -------------------------- | -------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- |
| `exchange`                 | String         | Clean venue id, e.g. `binance`, `deribit`, `okx` (no product suffix on this view)                                                                    |
| `market_symbol`            | String         | Universal symbol: `BASE-QUOTE-PERP` (linear perp), `BASE-QUOTE-PERP-INV` (inverse perp), `BASE-QUOTE-YYYY-MM-DD[-INV]` (dated). E.g. `BTC-USDT-PERP` |
| `underlying_asset`         | String         | Underlying asset (e.g. `BTC`)                                                                                                                        |
| `quote_asset`              | String         | Quote asset (e.g. `USDT`, `USD`)                                                                                                                     |
| `settling_asset`           | String         | Asset PnL settles in; equals the underlying ⟹ INVERSE contract                                                                                       |
| `denomination_asset`       | String         | Margin / collateral asset                                                                                                                            |
| `expiration`               | DateTime64(9)  | Contract expiry (null for perpetuals)                                                                                                                |
| `contract_size`            | Decimal(38,18) | Size per contract (e.g. 0.001)                                                                                                                       |
| `contract_type`            | String         | `LINEAR` (quote-settled) or `INVERSE` (coin-settled)                                                                                                 |
| `future_type`              | String         | `PERPETUAL` or `EXPIRING`                                                                                                                            |
| `exchange_specific_symbol` | String         | Raw exchange websocket ticker (e.g. `BTCUSDT_PERP`)                                                                                                  |

**Use Case Example**\
List perpetual BTC futures with linear pricing:

```sql
SELECT  
  exchange,  
  market_symbol,  
  underlying_asset,  
  contract_size  
FROM api.market_future  
WHERE underlying_asset = 'BTC'  
  AND future_type = 'PERPETUAL'  
  AND contract_type = 'LINEAR'  
```

***

### `api.market_option`

*Options contracts (e.g., BTC-USDT-2026-05-09-75000-C)*

| Column                     | Type           | Description                                                                                                                   |
| -------------------------- | -------------- | ----------------------------------------------------------------------------------------------------------------------------- |
| `exchange`                 | String         | Clean venue id, e.g. `deribit`, `okx`, `bybit`                                                                                |
| `market_symbol`            | String         | Universal symbol, format `BASE-QUOTE-YYYY-MM-DD-STRIKE-{C\|P}` (e.g. `BTC-USDT-2026-05-09-75000-C`, `BTC-BTC-...` on Deribit) |
| `underlying_asset`         | String         | Underlying asset (e.g. `BTC`)                                                                                                 |
| `quote_asset`              | String         | Quote asset (e.g. `USDT`, `USDC`, `USD`, `BTC`)                                                                               |
| `settling_asset`           | String         | Asset PnL settles in                                                                                                          |
| `expiration`               | DateTime64(9)  | Option expiry timestamp                                                                                                       |
| `strike`                   | Decimal(38,18) | Strike price (e.g. 75000)                                                                                                     |
| `contract_size`            | Decimal(38,18) | Size per contract (e.g. 0.1)                                                                                                  |
| `contract_type`            | String         | `LINEAR` or `INVERSE`                                                                                                         |
| `option_type`              | String         | `CALL` or `PUT`                                                                                                               |
| `exchange_specific_symbol` | String         | Raw exchange ticker (e.g. `BTC-30JUN23-30000-C`)                                                                              |

**Use Case Example**\
Find BTC call options expiring in the current quarter:

```sql
SELECT *
FROM api.market_option
WHERE underlying_asset = 'BTC'
  AND option_type = 'CALL'
  AND toYear(expiration) = toYear(now())
  AND toQuarter(expiration) = toQuarter(now())
ORDER BY expiration, strike
LIMIT 100
```

`toQuarter` and `toYear` are ClickHouse date-time functions — see the [`toQuarter`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toquarter) and [`toYear`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toyear) reference.

## REST API

## Get Spot Markets

> This endpoint allows you to obtain the full list of spot markets

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[],"paths":{"/market/spot":{"get":{"summary":"Get Spot Markets","description":"This endpoint allows you to obtain the full list of spot markets","tags":["market_data"],"responses":{"200":{"description":"All spot markets","content":{"application/json":{"schema":{"type":"array","items":{"$ref":"#/components/schemas/MarketSpot"}}}}}}}}},"components":{"schemas":{"MarketSpot":{"type":"object","properties":{"exchange":{"type":"string","description":"The name of the exchange"},"market_symbol":{"type":"string","description":"Universal name of market"},"base_asset":{"type":"string","description":"Universal name of the base asset"},"quote_asset":{"type":"string","description":"Universal name of the quote asset"},"exchange_specific_symbol":{"type":"string","description":"Name of the market on the exchange (as referenced by the exchange API)"}}}}}}
```

## Get Future Markets

> Returns expirable future and perpetual swaps.

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[],"paths":{"/market/future":{"get":{"summary":"Get Future Markets","description":"Returns expirable future and perpetual swaps.","tags":["market_data"],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"$ref":"#/components/schemas/MarketFuture"}}}}}}}}},"components":{"schemas":{"MarketFuture":{"type":"object","properties":{"exchange":{"type":"string","description":"The name of the exchange"},"market_symbol":{"type":"string","description":"Universal name of market"},"underlying_asset":{"type":"string","description":"Universal name of the underlying asset"},"quote_asset":{"type":"string","description":"Universal name of the quote asset"},"settling_asset":{"type":"string","description":"Universal name of the asset the future is settled in"},"denomination_asset":{"type":"string","description":"Universal name of the asset the future is denominated in"},"expiration":{"type":"string","format":"date-time","description":"UTC time of the contract expiration. Null for perpetual contracts except binance.\nNot-null for perpetual contracts that were delivered on delisting or not expired binance contracts.\n"},"contract_size":{"type":"number","format":"decimal","description":"Contract size in denomination_asset"},"contract_type":{"type":"string","enum":["LINEAR","INVERSE"],"description":"Type of contract (LINEAR or INVERSE)"},"future_type":{"type":"string","enum":["PERPETUAL","EXPIRING"],"description":"Type of future (PERPETUAL or EXPIRING)"}}}}}}
```

## GET /market/option/active

> Get active Option Markets

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[],"paths":{"/market/option/active":{"get":{"summary":"Get active Option Markets","tags":["market_data"],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"$ref":"#/components/schemas/MarketOption"}}}}}}}}},"components":{"schemas":{"MarketOption":{"type":"object","properties":{"exchange":{"type":"string","description":"The name of the exchange"},"market_symbol":{"type":"string","description":"Universal name of market"},"expiration":{"type":"string","format":"date-time","description":"UTC time of the option expiration"},"underlying_asset":{"type":"string","description":"Universal name of the underlying asset"},"quote_asset":{"type":"string","description":"Universal name of the quote asset"},"settling_asset":{"type":"string","description":"Universal name of the asset the option is settled in"},"denomination_asset":{"type":"string","description":"Universal name of the asset the option is denominated in"},"contract_size":{"type":"number","format":"decimal","description":"Contract size in denomination_asset"},"contract_type":{"type":"string","enum":["LINEAR","INVERSE"],"description":"Type of contract (LINEAR or INVERSE)"},"option_type":{"type":"string","enum":["CALL","PUT"],"description":"Type of option (CALL or PUT)"},"exchange_symbol":{"type":"string","description":"Name of the market on the exchange (as referenced by the exchange API)"}}}}}}
```

## Get all Option Markets

> This endpoint returns all option markets, including both active and expired options.\
> It is useful for retrieving a comprehensive list of all options available across exchanges.\
> \
> This endpoint is limited to 10000 instruments that expired before the \`expired\_before\` parameter in descending order of expiration.<br>

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[],"paths":{"/market/option/all":{"get":{"summary":"Get all Option Markets","description":"This endpoint returns all option markets, including both active and expired options.\nIt is useful for retrieving a comprehensive list of all options available across exchanges.\n\nThis endpoint is limited to 10000 instruments that expired before the `expired_before` parameter in descending order of expiration.\n","tags":["market_data"],"parameters":[{"name":"exchange","in":"query","required":true,"description":"Filter by exchange name.\nExample: 'deribit', 'delta'\n","schema":{"type":"string","enum":["deribit","okx","delta"]}},{"name":"expired_before","in":"query","required":true,"description":"Filter options that expired before a specific date. Accepts a plain\ndate (`2024-12-31`, treated as midnight UTC) or a full ISO 8601\ntimestamp (`2024-12-31T23:59:59Z`).\n","schema":{"type":"string","format":"date-time"}}],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"$ref":"#/components/schemas/MarketOption"}}}}}}}}},"components":{"schemas":{"MarketOption":{"type":"object","properties":{"exchange":{"type":"string","description":"The name of the exchange"},"market_symbol":{"type":"string","description":"Universal name of market"},"expiration":{"type":"string","format":"date-time","description":"UTC time of the option expiration"},"underlying_asset":{"type":"string","description":"Universal name of the underlying asset"},"quote_asset":{"type":"string","description":"Universal name of the quote asset"},"settling_asset":{"type":"string","description":"Universal name of the asset the option is settled in"},"denomination_asset":{"type":"string","description":"Universal name of the asset the option is denominated in"},"contract_size":{"type":"number","format":"decimal","description":"Contract size in denomination_asset"},"contract_type":{"type":"string","enum":["LINEAR","INVERSE"],"description":"Type of contract (LINEAR or INVERSE)"},"option_type":{"type":"string","enum":["CALL","PUT"],"description":"Type of option (CALL or PUT)"},"exchange_symbol":{"type":"string","description":"Name of the market on the exchange (as referenced by the exchange API)"}}}}}}
```

## The MarketSpot object

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"components":{"schemas":{"MarketSpot":{"type":"object","properties":{"exchange":{"type":"string","description":"The name of the exchange"},"market_symbol":{"type":"string","description":"Universal name of market"},"base_asset":{"type":"string","description":"Universal name of the base asset"},"quote_asset":{"type":"string","description":"Universal name of the quote asset"},"exchange_specific_symbol":{"type":"string","description":"Name of the market on the exchange (as referenced by the exchange API)"}}}}}}
```

## The MarketFuture object

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"components":{"schemas":{"MarketFuture":{"type":"object","properties":{"exchange":{"type":"string","description":"The name of the exchange"},"market_symbol":{"type":"string","description":"Universal name of market"},"underlying_asset":{"type":"string","description":"Universal name of the underlying asset"},"quote_asset":{"type":"string","description":"Universal name of the quote asset"},"settling_asset":{"type":"string","description":"Universal name of the asset the future is settled in"},"denomination_asset":{"type":"string","description":"Universal name of the asset the future is denominated in"},"expiration":{"type":"string","format":"date-time","description":"UTC time of the contract expiration. Null for perpetual contracts except binance.\nNot-null for perpetual contracts that were delivered on delisting or not expired binance contracts.\n"},"contract_size":{"type":"number","format":"decimal","description":"Contract size in denomination_asset"},"contract_type":{"type":"string","enum":["LINEAR","INVERSE"],"description":"Type of contract (LINEAR or INVERSE)"},"future_type":{"type":"string","enum":["PERPETUAL","EXPIRING"],"description":"Type of future (PERPETUAL or EXPIRING)"}}}}}}
```

## The MarketOption object

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"components":{"schemas":{"MarketOption":{"type":"object","properties":{"exchange":{"type":"string","description":"The name of the exchange"},"market_symbol":{"type":"string","description":"Universal name of market"},"expiration":{"type":"string","format":"date-time","description":"UTC time of the option expiration"},"underlying_asset":{"type":"string","description":"Universal name of the underlying asset"},"quote_asset":{"type":"string","description":"Universal name of the quote asset"},"settling_asset":{"type":"string","description":"Universal name of the asset the option is settled in"},"denomination_asset":{"type":"string","description":"Universal name of the asset the option is denominated in"},"contract_size":{"type":"number","format":"decimal","description":"Contract size in denomination_asset"},"contract_type":{"type":"string","enum":["LINEAR","INVERSE"],"description":"Type of contract (LINEAR or INVERSE)"},"option_type":{"type":"string","enum":["CALL","PUT"],"description":"Type of option (CALL or PUT)"},"exchange_symbol":{"type":"string","description":"Name of the market on the exchange (as referenced by the exchange API)"}}}}}}
```


# Public trades

This view provides access to raw trade ticks across all supported exchanges, normalized into a consistent schema with unified market symbols.

## SQL API

### `api.trade`

Raw trade ticks across all supported exchanges, normalized into a consistent schema with unified market symbols.

#### Columns

| Column      | Type           | Description                                                                                                                                            |
| ----------- | -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `exchange`  | String         | Exchange id, suffixed by product (spot `binance`; linear future `binance-usdm-future`; inverse future `binance-coinm-future`; option `deribit-option`) |
| `market`    | String         | Universal symbol (e.g., "BTC-USDT")                                                                                                                    |
| `side`      | String         | "buy" or "sell"                                                                                                                                        |
| `quantity`  | Decimal(76,20) | Base asset amount traded (high precision)                                                                                                              |
| `price`     | Decimal(76,20) | Quote asset price per unit (high precision)                                                                                                            |
| `timestamp` | DateTime64     | Trade execution time (nanosecond precision)                                                                                                            |
| `trade_id`  | String         | Unique trade identifier (exchange-specific)                                                                                                            |

***

#### Data Access by Tier

| Tier         | Spot Trades     | Futures Trades  | Options Trades  |
| ------------ | --------------- | --------------- | --------------- |
| Free         | Rolling 24h     | Rolling 24h     | Rolling 24h     |
| Developer    | Rolling 90 days | Rolling 90 days | Rolling 90 days |
| Professional | Rolling 1 year  | Rolling 1 year  | Rolling 1 year  |
| Business     | Full history    | Full history    | Full history    |
| Enterprise   | Full history    | Full history    | Full history    |

{% hint style="info" %}
A request that partially overlaps your tier's window returns only the in-window data. On the REST API (`/trade`), a request whose **entire** range is older than your window returns **HTTP 422** with a working `example_url` and a `discord_url` instead of an empty response. See [Pricing](/pricing) for details.
{% endhint %}

***

#### Performance

The underlying `public_data.trade` table:

* Processes **>500,000 trades/second**
* Stores **20+ TB of historical data**
* This requires **strict filtering** during queries:
  * `timestamp` (always use time ranges)
  * `market` (single market per query recommended)
  * `exchange`

### Example Queries

#### 1. Recent Trades for a Single Market

```sql
SELECT 
  *
FROM api.trade  
WHERE market = 'BTC-USD'
  AND exchange = 'coinbase'
  AND timestamp >= now() - INTERVAL 5 MINUTE
ORDER BY timestamp DESC
LIMIT 10
```

`BTC-USD` (Coinbase/Kraken/Bitstamp/Gemini/Bitfinex) and `BTC-USDT` (Binance/OKX/Bybit/KuCoin/Gate.io) are distinct markets — there is no cross-venue quote unification.

Functions used: [`now`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#now).

*Output:*

```
┌─exchange─┬─market──┬─side─┬───quantity─┬─────price─┬─────────────────────timestamp─┬─trade_id──┐
│ coinbase │ BTC-USD │ sell │ 0.00003983 │  119071.8 │ 2025-08-15 11:24:01.151094000 │ 861973726 │
│ coinbase │ BTC-USD │ sell │ 0.00024491 │  119071.8 │ 2025-08-15 11:24:01.144325000 │ 861973725 │
│ coinbase │ BTC-USD │ buy  │    0.00042 │  119072.6 │ 2025-08-15 11:24:01.114930000 │ 861973724 │
│ coinbase │ BTC-USD │ sell │ 0.00016325 │ 119068.19 │ 2025-08-15 11:24:01.058078000 │ 861973723 │
│ coinbase │ BTC-USD │ sell │ 0.00686584 │ 119056.69 │ 2025-08-15 11:24:01.040272000 │ 861973722 │
│ coinbase │ BTC-USD │ sell │ 0.00015934 │ 119056.69 │ 2025-08-15 11:24:00.801192000 │ 861973721 │
│ coinbase │ BTC-USD │ sell │ 0.00016498 │ 119056.69 │ 2025-08-15 11:24:00.537027000 │ 861973720 │
│ coinbase │ BTC-USD │ sell │ 0.00000134 │ 119056.58 │ 2025-08-15 11:24:00.537027000 │ 861973719 │
│ coinbase │ BTC-USD │ buy  │ 0.00099849 │ 119051.89 │ 2025-08-15 11:24:00.336929000 │ 861973718 │
│ coinbase │ BTC-USD │ sell │ 0.00000562 │ 119045.95 │ 2025-08-15 11:24:00.323215000 │ 861973717 │
└──────────┴─────────┴──────┴────────────┴───────────┴───────────────────────────────┴───────────┘
```

#### 2. Large Trade Detection (Whale Watching)

Find all trades over $100k on SOL-USDT

```sql
SELECT *
FROM (
  SELECT 
    *,
    quantity * price AS notional
  FROM api.trade
  WHERE market = 'SOL-USDT'
    AND timestamp between '2025-08-15 09:00:00' and '2025-08-15 13:00:00'
)
WHERE notional > 100000  -- $100k+ trades
ORDER BY notional DESC
```

*Output*

```
┌─exchange─┬─market───┬─side─┬───quantity─┬──price─┬─────────────────────timestamp─┬─trade_id───┬──────notional─┐
│ binance  │ SOL-USDT │ buy  │       1008 │ 195.26 │ 2025-08-15 09:28:18.593000000 │ 1473383529 │     196822.08 │
│ okx      │ SOL-USDT │ buy  │ 731.421184 │  194.5 │ 2025-08-15 10:54:50.870000000 │ 333599582  │ 142261.420288 │
│ binance  │ SOL-USDT │ sell │    641.752 │    195 │ 2025-08-15 09:29:49.731000000 │ 1473387507 │     125141.64 │
│ binance  │ SOL-USDT │ sell │    593.482 │  195.7 │ 2025-08-15 09:26:15.271000000 │ 1473378734 │   116144.4274 │
│ binance  │ SOL-USDT │ sell │    583.196 │ 195.02 │ 2025-08-15 09:29:49.779000000 │ 1473387622 │  113734.88392 │
└──────────┴──────────┴──────┴────────────┴────────┴───────────────────────────────┴────────────┴───────────────┘
```

#### 3. Trade Imbalance Analysis

Calculate 5-second buy/sell pressure

```sql

SELECT
  market,
  toStartOfInterval(timestamp, INTERVAL 5 SECOND) AS period,
  sumIf(quantity, side = 'buy') AS buy_vol,
  sumIf(quantity, side = 'sell') AS sell_vol,
  (buy_vol - sell_vol) / (buy_vol + sell_vol) AS imbalance_ratio
FROM api.trade
WHERE market = 'BTC-USDT'
  AND exchange = 'binance'
  AND timestamp BETWEEN 
      '2024-06-15 12:00:00' AND 
      '2024-06-15 12:05:00'
GROUP BY market, period
HAVING (buy_vol + sell_vol) > 1  -- Ignore illiquid periods
```

Functions used: [`toStartOfInterval`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofinterval), [`sumIf`](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-if).

*Output:*

```
┌─market───┬──────────────period─┬─buy_vol─┬─sell_vol─┬─────────imbalance_ratio─┐
│ BTC-USDT │ 2024-06-15 12:03:35 │ 4.78761 │   0.2771 │  0.89057616329464075929 │
│ BTC-USDT │ 2024-06-15 12:00:15 │ 0.00694 │  1.81214 │ -0.99236976933394902917 │
│ BTC-USDT │ 2024-06-15 12:01:55 │ 0.45068 │  1.61039 │ -0.56267375683504199275 │
│ BTC-USDT │ 2024-06-15 12:04:25 │ 0.32506 │ 11.52658 │ -0.94514514446945739155 │
│ BTC-USDT │ 2024-06-15 12:03:45 │ 5.56966 │  0.02014 │  0.99279401767505098572 │
└──────────┴─────────────────────┴─────────┴──────────┴─────────────────────────┘
```

## REST API

## Get public trade for a Market

> This endpoint retrieves public trade data for a specific market.\
> \
> It includes spot, future, and option markets.\
> \
> This endpoint is limited to 10000 trades per request.<br>

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[{"apiKeyAuth":[]}],"components":{"securitySchemes":{"apiKeyAuth":{"type":"apiKey","in":"header","name":"x-api-key","description":"Required for /ohlcv and /trade endpoints.\nPass your API key in the x-api-key header.\nPublic /market/* endpoints do not require authentication.\n"}}},"paths":{"/trade":{"get":{"summary":"Get public trade for a Market","description":"This endpoint retrieves public trade data for a specific market.\n\nIt includes spot, future, and option markets.\n\nThis endpoint is limited to 10000 trades per request.\n","tags":["market_data"],"parameters":[{"name":"exchange","in":"query","required":true,"description":"The name of the exchange to filter by","schema":{"type":"string"}},{"name":"market","in":"query","required":true,"description":"The universal market symbol to filter by","schema":{"type":"string"}},{"name":"start_datetime","in":"query","required":true,"description":"The start time for the trade data. Accepts any format parseable by\n`parseDateTime64BestEffort` — e.g. a plain date (`2024-06-01`, treated\nas midnight UTC) or a full ISO 8601 timestamp (`2024-06-01T12:00:00Z`).\n","schema":{"type":"string","format":"date-time"}},{"name":"end_datetime","in":"query","required":true,"description":"The end time for the trade data. Accepts any format parseable by\n`parseDateTime64BestEffort` — e.g. a plain date (`2024-06-02`, treated\nas midnight UTC) or a full ISO 8601 timestamp (`2024-06-02T12:00:00Z`).\n","schema":{"type":"string","format":"date-time"}}],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"type":"object","properties":{"exchange":{"type":"string","description":"The name of the exchange"},"market":{"type":"string","description":"The universal market symbol"},"side":{"type":"string","enum":["buy","sell"],"description":"The side of the trade (buy or sell)"},"quantity":{"type":"number","format":"decimal","description":"The quantity of the asset traded"},"price":{"type":"number","format":"decimal","description":"The price at which the trade occurred"},"timestamp":{"type":"string","format":"date-time","description":"The datetime when the trade occurred"},"trade_id":{"type":"string","description":"Exchange provided unique identifier for the trade"}}}}}}},"422":{"description":"Empty result because the entire requested range is older than your tier's data window; the body carries a working example_url and a Discord invite.","content":{"application/json":{"schema":{"type":"object","properties":{"message":{"type":"string","description":"Human-readable explanation of the empty result"},"example_url":{"type":"string","description":"A ready-to-run query within your tier's window"},"discord_url":{"type":"string","description":"Invite to the Koinju Discord for help"}}}}}}}}}}}
```


# OHLCV

This parameterized view provides on-the-fly aggregation of raw 1-minute candles into customizable time intervals (e.g., 5min, 1hr, 4hr).

## SQL API

### `api.ohlcv`

Aggregated OHLCV candles with dynamic resolution — on-the-fly aggregation of raw 1-minute candles into customizable time intervals.

#### Parameters

| Parameter                    | Type  | Description                         |
| ---------------------------- | ----- | ----------------------------------- |
| `candle_duration_in_minutes` | Int32 | Aggregation window (1, 5, 15, etc.) |

#### Columns

| Column             | Type           | Description                               |
| ------------------ | -------------- | ----------------------------------------- |
| `start`            | DateTime       | Candle open time (UTC)                    |
| `end`              | DateTime       | Candle close time (UTC)                   |
| `exchange`         | String         | Exchange name (e.g., "binance")           |
| `market`           | String         | Universal symbol (e.g., "BTC-USDT")       |
| `open`             | Decimal(76,20) | First price in interval                   |
| `high`             | Decimal(76,20) | Highest price in interval                 |
| `low`              | Decimal(76,20) | Lowest price in interval                  |
| `close`            | Decimal(76,20) | Last price in interval                    |
| `volume`           | Decimal(76,20) | Total base asset volume                   |
| `count`            | Int32          | Number of trades                          |
| `duration_minutes` | Int32          | Candle duration (matches input parameter) |

***

#### Data Access by Tier

| Data                                 | Free            | Developer      | Professional | Business     | Enterprise   |
| ------------------------------------ | --------------- | -------------- | ------------ | ------------ | ------------ |
| Daily/Hourly OHLCV                   | Full history    | Full history   | Full history | Full history | Full history |
| Spot 1-min OHLCV                     | Rolling 1 month | Rolling 1 year | Full history | Full history | Full history |
| Futures OHLCV (1-min and aggregates) | Rolling 1 month | Rolling 1 year | Full history | Full history | Full history |
| Options OHLCV (1-min and aggregates) | Rolling 1 month | Rolling 1 year | Full history | Full history | Full history |

{% hint style="info" %}
A request that partially overlaps your tier's window returns only the in-window data. On the REST API (`/ohlcv`), a request whose **entire** range is older than your window returns **HTTP 422** with a working `example_url` and a `discord_url` instead of an empty response. See [Pricing](/pricing) for details.
{% endhint %}

***

#### Performance

The underlying `public_data.candle_1m` table exceeds **4 TB**. Always include:

1. **Time filters** (e.g., `start BETWEEN ...`)
2. **Market/exchange filters**
3. **Reasonable aggregation windows**

Unfiltered queries will be rejected by the query killer!

***

### Example Queries

#### 1. Basic Usage: 7 days of daily ETH Candles (Binance)

```sql
SELECT
  start,
  open,
  high,
  low,
  close,
  volume  
FROM api.ohlcv(candle_duration_in_minutes = 1440)  
WHERE market = 'ETH-USDT'
  AND exchange = 'binance'
  AND start >= '2024-06-01'
  AND start <= '2024-06-08'
```

`market` symbols are quote-specific and not unified across venues: `BTC-USD` (Coinbase/Kraken/Bitstamp/Gemini/Bitfinex) and `BTC-USDT` (Binance/OKX/Bybit/KuCoin/Gate.io) are distinct markets.

#### 2. Exchange Volume Leaderboard

Total BTC volume this month per exchange

```sql
SELECT 
  exchange, 
  sum(volume) AS total_volume 
FROM api.ohlcv(candle_duration_in_minutes=1) 
WHERE market IN ('BTC-USD', 'BTC-USDT')
  AND start BETWEEN 
      toStartOfMonth(now()) AND 
      now()
GROUP BY exchange
ORDER BY total_volume DESC
```

USD- and USDT-quoted books are distinct markets, so a cross-exchange leaderboard must include both symbols.

Functions used: [`toStartOfMonth`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofmonth), [`now`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#now).

*Output:*

```
┌─exchange───┬─total_volume─┐
│ binance    │   1542032.11 │
│ kraken     │    892384.75 │
│ coinbase   │    784291.03 │
└────────────┴──────────────┘
```

#### 3. Volatility Analysis: 4hr ATR

```sql
WITH candles AS (
  SELECT 
    *,
    high - low AS true_range  
  FROM api.ohlcv(candle_duration_in_minutes = 240)  -- 4hr candles
  WHERE market IN ('BTC-USD', 'BTC-USDT')
    AND start >= now() - INTERVAL 7 DAY
)
SELECT 
  start,
  true_range,
  avg(true_range) OVER (ORDER BY start ROWS 14 PRECEDING) AS atr_14_period
FROM candles
```

USD- and USDT-quoted BTC are distinct markets; scope to both (or pin a single venue with `exchange = '…'`) to avoid mixing books.

Functions used: [`avg() OVER`](https://clickhouse.com/docs/sql-reference/window-functions).

*Output:*

```
┌───────────────start─┬─true_range─┬──────atr_14_period─┐
│ 2025-08-08 16:00:00 │      876.5 │              876.5 │
│ 2025-08-08 20:00:00 │      630.1 │  753.3000000000001 │
│ 2025-08-09 00:00:00 │      333.1 │  613.2333333333333 │
│ 2025-08-09 04:00:00 │      549.5 │              597.3 │
│ 2025-08-09 08:00:00 │     1106.2 │             699.08 │
│ 2025-08-09 12:00:00 │      494.8 │  665.0333333333334 │
│ 2025-08-09 16:00:00 │      396.2 │  626.6285714285714 │
│ 2025-08-09 20:00:00 │      686.9 │  634.1624999999999 │
│ 2025-08-10 00:00:00 │     2012.7 │  787.3333333333335 │
│ 2025-08-10 04:00:00 │     1211.8 │             829.78 │
│ 2025-08-10 08:00:00 │      823.7 │  829.2272727272727 │

```

#### 4. Fill gaps

Low volume candles are not backfilled by default. This can be done on query:

```sql
SELECT
  start,
  open,
  high,
  low,
   close,
  volume  
FROM api.ohlcv(candle_duration_in_minutes = 1)  
WHERE market = 'BTC-MXN'
  AND exchange = 'binance'
  AND start >= '2024-06-01'
  AND start <= '2024-06-02'
  order by start
  WITH FILL
  STEP interval 1 minute  
  INTERPOLATE(open as close, high as close, low as close , close as close);
```

Functions used: [`WITH FILL`](https://clickhouse.com/docs/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier).

*Output*

```
┌───────────────start─┬────open─┬────high─┬─────low─┬───close─┬───volume─┐
│ 2024-06-01 00:18:00 │ 1155300 │ 1155300 │ 1155300 │ 1155300 │ 0.000214 │
│ 2024-06-01 00:19:00 │ 1155300 │ 1155300 │ 1154806 │ 1154806 │ 0.000921 │
│ 2024-06-01 00:20:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
│ 2024-06-01 00:21:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
│ 2024-06-01 00:22:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
│ 2024-06-01 00:23:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
│ 2024-06-01 00:24:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
│ 2024-06-01 00:25:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
```

## REST API

## Get OHLCV for a Market

> This endpoint retrieves OHLCV (Open, High, Low, Close, Volume) data for a specific market.\
> \
> It includes spot, future, and option markets.\
> \
> This endpoint is limited to 10000 candles per request.<br>

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[{"apiKeyAuth":[]}],"components":{"securitySchemes":{"apiKeyAuth":{"type":"apiKey","in":"header","name":"x-api-key","description":"Required for /ohlcv and /trade endpoints.\nPass your API key in the x-api-key header.\nPublic /market/* endpoints do not require authentication.\n"}}},"paths":{"/ohlcv":{"get":{"summary":"Get OHLCV for a Market","description":"This endpoint retrieves OHLCV (Open, High, Low, Close, Volume) data for a specific market.\n\nIt includes spot, future, and option markets.\n\nThis endpoint is limited to 10000 candles per request.\n","tags":["market_data"],"parameters":[{"name":"exchange","in":"query","required":true,"description":"The name of the exchange to filter by","schema":{"type":"string"}},{"name":"market","in":"query","required":true,"description":"The universal market symbol to filter by","schema":{"type":"string"}},{"name":"candle_duration_in_minutes","in":"query","required":true,"description":"The time interval for the candles in minutes.\nThe value can be any amount of minutes:\n- 1: 1 minute\n- 5: 5 minutes\n- 60: 1 hour\n- 1440: 1 day\n","schema":{"type":"integer","minimum":1,"maximum":1440}},{"name":"start_datetime","in":"query","required":true,"description":"The start time for the OHLCV data. Accepts any format parseable by\n`parseDateTime64BestEffort` — e.g. a plain date (`2024-06-01`, treated\nas midnight UTC) or a full ISO 8601 timestamp (`2024-06-01T12:00:00Z`).\n","schema":{"type":"string","format":"date-time"}},{"name":"end_datetime","in":"query","required":true,"description":"The end time for the OHLCV data. Accepts any format parseable by\n`parseDateTime64BestEffort` — e.g. a plain date (`2024-06-08`, treated\nas midnight UTC) or a full ISO 8601 timestamp (`2024-06-08T12:00:00Z`).\n","schema":{"type":"string","format":"date-time"}}],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"type":"object","properties":{"start":{"type":"string","format":"date-time","description":"Start time of the candle"},"end":{"type":"string","format":"date-time","description":"End time of the candle"},"duration_minutes":{"type":"integer","description":"Duration of the candle in minutes"},"open":{"type":"number","format":"decimal","description":"Opening price of the candle"},"high":{"type":"number","format":"decimal","description":"Highest price during the candle period"},"low":{"type":"number","format":"decimal","description":"Lowest price during the candle period"},"close":{"type":"number","format":"decimal","description":"Closing price of the candle"},"volume":{"type":"number","format":"decimal","description":"Volume traded during the candle period"},"count":{"type":"integer","description":"Number of trades during the candle period"}}}}}}},"422":{"description":"Empty result because the entire requested range is older than your tier's data window; the body carries a working example_url and a Discord invite.","content":{"application/json":{"schema":{"type":"object","properties":{"message":{"type":"string","description":"Human-readable explanation of the empty result"},"example_url":{"type":"string","description":"A ready-to-run query within your tier's window"},"discord_url":{"type":"string","description":"Invite to the Koinju Discord for help"}}}}}}}}}}}
```


# Funding rate

## SQL API

### `api.funding_rate`

Funding-rate history for perpetual swaps across supported exchanges. Markets are exposed under their **universal symbol** (e.g. `BTC-USD-PERP-INV` for the BTC-margined inverse perp, `BTC-USDT-PERP` for the USDT-margined linear), with the originating exchange and contract type recorded alongside the rate. Inverse perps carry an `-INV` suffix to keep them distinct from any linear product with the same `(base, quote)` pair.

The settlement period varies per exchange. Deribit publishes funding **hourly** with continuous accrual; Binance, OKX, and Bybit publish **discrete 8-hour events** (some markets settle every 4h or 1h). Consumers should infer the period from the gap between consecutive timestamps for the same `(exchange, market)` pair.

> The REST `/funding-rate` endpoint requires both `market` and `exchange`. For cross-exchange comparisons in a single query, use the SQL API.

#### Columns

| Column          | Type                   | Description                                                                      |
| --------------- | ---------------------- | -------------------------------------------------------------------------------- |
| `exchange`      | LowCardinality(String) | Exchange that published the funding event (`binance`, `bybit`, `okx`, `deribit`) |
| `contract_type` | LowCardinality(String) | `LINEAR_PERPETUAL` (USDT/USDC-margined) or `INVERSE_PERPETUAL` (coin-margined)   |
| `market`        | LowCardinality(String) | Universal perpetual symbol (e.g. `BTC-USD-PERP-INV`, `ETH-USDT-PERP`)            |
| `timestamp`     | DateTime64(9, 'UTC')   | Settlement timestamp                                                             |
| `funding_rate`  | Decimal(76, 20)        | Rate that accrued in the period ending at `timestamp`                            |

### Example Queries

#### 1. Latest BTC-USD-PERP-INV funding across exchanges

```sql
SELECT
    timestamp,
    exchange,
    contract_type,
    funding_rate
FROM api.funding_rate
WHERE market = 'BTC-USD-PERP-INV'
  AND timestamp >= now() - INTERVAL 24 HOUR
ORDER BY timestamp DESC, exchange ASC
LIMIT 50
```

Functions used: [`now`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#now).

#### 2. Cross-exchange funding spread

Compare the most recent settled funding rate per exchange for one market:

```sql
SELECT
    exchange,
    argMax(funding_rate, timestamp) AS last_rate,
    max(timestamp) AS last_settled_at
FROM api.funding_rate
WHERE market = 'BTC-USD-PERP-INV'
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY exchange
ORDER BY last_rate DESC
```

Functions used: [`argMax`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/argmax).

#### 3. Realized 24h funding cost on a long position

```sql
SELECT
    market,
    exchange,
    sum(funding_rate) AS realized_24h_funding
FROM api.funding_rate
WHERE timestamp >= now() - INTERVAL 24 HOUR
  AND market IN ('BTC-USD-PERP-INV', 'ETH-USD-PERP-INV', 'SOL-USD-PERP-INV')
GROUP BY market, exchange
ORDER BY market, exchange
```

A long pays funding when `funding_rate > 0` and receives it when `funding_rate < 0`. Multiply by position notional to convert to fiat.

## REST API

## Get funding rates for a perpetual market

> Funding-rate history for perpetual swaps across supported exchanges.\
> \
> The settlement period is exchange-specific. Deribit publishes funding\
> hourly (continuous accrual); Binance, OKX, and Bybit publish discrete\
> funding events every 8 hours (sometimes 4h or 1h on select markets).\
> Consumers should infer the period from the gap between consecutive\
> timestamps for the same \`(exchange, market)\` pair.\
> \
> Supported \`exchange\` values: \`binance\`, \`bybit\`, \`okx\`, \`deribit\`.\
> For cross-exchange queries, use the SQL API.\
> \
> This endpoint is limited to 10000 records per request.<br>

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[{"apiKeyAuth":[]}],"components":{"securitySchemes":{"apiKeyAuth":{"type":"apiKey","in":"header","name":"x-api-key","description":"Required for /ohlcv and /trade endpoints.\nPass your API key in the x-api-key header.\nPublic /market/* endpoints do not require authentication.\n"}}},"paths":{"/funding-rate":{"get":{"summary":"Get funding rates for a perpetual market","description":"Funding-rate history for perpetual swaps across supported exchanges.\n\nThe settlement period is exchange-specific. Deribit publishes funding\nhourly (continuous accrual); Binance, OKX, and Bybit publish discrete\nfunding events every 8 hours (sometimes 4h or 1h on select markets).\nConsumers should infer the period from the gap between consecutive\ntimestamps for the same `(exchange, market)` pair.\n\nSupported `exchange` values: `binance`, `bybit`, `okx`, `deribit`.\nFor cross-exchange queries, use the SQL API.\n\nThis endpoint is limited to 10000 records per request.\n","tags":["market_data"],"parameters":[{"name":"market","in":"query","required":true,"description":"Koinju canonical perpetual symbol. Inverse perps carry the `-INV`\nsuffix (e.g. `BTC-USD-PERP-INV` for the BTC-margined USD-quoted\nperp); linear perps don't (`BTC-USDT-PERP`). Use `/market/future`\nto enumerate available perpetual symbols.\n","schema":{"type":"string"}},{"name":"exchange","in":"query","required":true,"description":"Exchange to filter by — one of `binance`, `bybit`, `okx`, `deribit`.\nFor cross-exchange queries, use the SQL API.\n","schema":{"type":"string","enum":["binance","bybit","okx","deribit"]}},{"name":"start_datetime","in":"query","required":true,"description":"Window start. Accepts any format parseable by `parseDateTime64BestEffort`\n— e.g. a plain date (`2026-05-01`, treated as midnight UTC) or a full\nISO 8601 timestamp (`2026-05-01T12:00:00Z`).\n","schema":{"type":"string","format":"date-time"}},{"name":"end_datetime","in":"query","required":true,"description":"Window end. Same format as `start_datetime`. Returned rows satisfy\n`start_datetime <= timestamp < end_datetime`.\n","schema":{"type":"string","format":"date-time"}}],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"type":"object","properties":{"time":{"type":"string","format":"date-time","description":"Settlement timestamp (UTC)"},"exchange":{"type":"string","description":"Exchange that published this funding event"},"market":{"type":"string","description":"Koinju canonical perpetual symbol"},"funding_rate":{"type":"number","format":"decimal","description":"Funding rate that accrued in the period ending at `time`.\nPeriod varies per exchange (8h for Binance/OKX/Bybit, 1h for Deribit).\n"}}}}}}}}}}}}
```


# Volatility index

## SQL API

### `public_data.volatility_index`

Minute-resolution OHLC bars of the volatility index published by an exchange. Currently the primary source is **Deribit's DVOL** for `BTC` and `ETH` — a 30-day forward-looking implied-volatility index analogous to the VIX in traditional finance.

#### Columns

| Column      | Type                   | Description                                   |
| ----------- | ---------------------- | --------------------------------------------- |
| `exchange`  | LowCardinality(String) | Exchange that publishes the index (`deribit`) |
| `market`    | LowCardinality(String) | Underlying asset (`BTC`, `ETH`)               |
| `timestamp` | DateTime64(9, 'UTC')   | Bar timestamp                                 |
| `open`      | Decimal(76, 20)        | Open value of the index for the bar           |
| `high`      | Decimal(76, 20)        | High value                                    |
| `low`       | Decimal(76, 20)        | Low value                                     |
| `close`     | Decimal(76, 20)        | Close value                                   |

The index is quoted in **annualised volatility percentage points**: a value of `55.21` means a 55.21% annualised IV.

### Example Queries

#### 1. Latest BTC and ETH DVOL

```sql
SELECT
    timestamp,
    market,
    close
FROM public_data.volatility_index
WHERE exchange = 'deribit'
  AND market IN ('BTC', 'ETH')
  AND timestamp >= now() - INTERVAL 24 HOUR
ORDER BY timestamp DESC, market ASC
LIMIT 50
```

#### 2. 1-hour resampled DVOL

The raw bars are at 1-minute resolution. Aggregate to whatever interval you need:

```sql
SELECT
    toStartOfHour(timestamp) AS hour,
    market,
    argMin(open, timestamp)  AS open,
    max(high)                AS high,
    min(low)                 AS low,
    argMax(close, timestamp) AS close
FROM public_data.volatility_index
WHERE exchange = 'deribit'
  AND market = 'BTC'
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY hour, market
ORDER BY hour ASC
```

Functions used: [`toStartOfHour`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofhour), [`argMin`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/argmin), [`argMax`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/argmax).

#### 3. Variance risk premium (DVOL forecast vs forward-realised)

Compare the DVOL forecast on day `D` against the realised vol that actually unfolded over the next 30 days `(D, D+30]`. The difference is the **ex-post variance risk premium** — what a delta-hedged option seller pocketed (or paid) on a 30-day book opened at `D`. This is the textbook implied-vs-realised comparison and the basis for short-vol carry strategies.

The trick is the forward window — `ROWS BETWEEN 1 FOLLOWING AND 30 FOLLOWING` flips the usual trailing rolling-stddev around so each row's realised value is computed from the 30 days **after** it. The result drops 30 days from the recent end (you can't measure the realised vs a forecast whose window hasn't elapsed yet).

```sql
WITH
    dvol AS (
        SELECT toStartOfDay(timestamp) AS day, avg(close) AS dvol_close
        FROM public_data.volatility_index
        WHERE exchange = 'deribit' AND market = 'BTC'
          AND timestamp >= toDate(now()) - INTERVAL 13 MONTH
          AND timestamp <  toDate(now()) - INTERVAL 30 DAY
        GROUP BY day
    ),
    returns AS (
        SELECT toStartOfDay(start) AS day, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance' AND market = 'BTC-USDT'
          AND start >= toDate(now()) - INTERVAL 13 MONTH - INTERVAL 1 DAY
    ),
    log_returns AS (
        SELECT day, log(close / lagInFrame(close, 1) OVER (ORDER BY day)) AS r
        FROM returns
    ),
    forward_rv AS (
        SELECT
            day,
            100 * sqrt(365) * stddevSamp(r) OVER (
                ORDER BY day ROWS BETWEEN 1 FOLLOWING AND 30 FOLLOWING
            ) AS rv_next_30d
        FROM log_returns
    )
SELECT
    d.day,
    d.dvol_close                           AS dvol_forecast,
    f.rv_next_30d                          AS realised_next_30d,
    d.dvol_close - f.rv_next_30d           AS premium
FROM dvol d
JOIN forward_rv f USING (day)
WHERE f.rv_next_30d IS NOT NULL
ORDER BY d.day DESC
```

Functions used: [`toStartOfDay`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofday), [`toDate`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#todate), [`toFloat64`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tofloat64), [`log`](https://clickhouse.com/docs/sql-reference/functions/math-functions#log), [`sqrt`](https://clickhouse.com/docs/sql-reference/functions/math-functions#sqrt), [`lagInFrame`](https://clickhouse.com/docs/sql-reference/window-functions#laginframe), [`stddevSamp`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevsamp).

#### Output (rolling 365 days, BTC)

A short summary across one year of forecasts:

| metric                     | value                      |
| -------------------------- | -------------------------- |
| mean premium               | +4.2 vol pts               |
| median premium             | +9.3 vol pts               |
| days with negative premium | 93 / 365 (≈25%)            |
| largest positive premium   | +24.1 vol pts (2025-12-07) |
| largest negative premium   | −45.1 vol pts (2026-01-28) |

Two things to read from this:

1. **Mean is positive but small; the median is materially higher.** That's the variance risk premium signature — most days the forecast over-prices what unfolds, but a handful of tail months pull the mean down. Selling 30-day vol on a typical day is profitable; doing it indiscriminately is not.
2. **Tail events show up clearly.** Late January 2026 had realised vol over 80% while DVOL was sitting around 38% — the forecast missed by 40+ vol points. A delta-hedged short straddle opened on those days lost roughly that spread, scaled by vega and time. The kind of event short-vol books exist to survive (or fail to).

#### Notes

* The query uses **calendar days** (`stddevSamp` over 30 forward bars). For trading-day RV, replace the daily candles with 5-trading-day-week filters upstream.
* The rolling 30-day stddev is sample (Bessel-corrected). Use [`stddevPop`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevpop) if you prefer the population estimator — the 1/29 vs 1/30 normalization difference is well under a basis point for our window sizes.
* If you want the **forward annualised RV in basis-point premium terms** (instead of vol points), divide by `dvol_forecast` and multiply by 1e4. Useful for ranking days by relative misforecast magnitude.

## REST API

## Get volatility index OHLC

> Returns OHLC (open / high / low / close) of an exchange's published\
> volatility index for a given underlying. Currently the primary\
> source is Deribit's DVOL (BTC and ETH).\
> \
> This endpoint is limited to 10000 records per request.<br>

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[{"apiKeyAuth":[]}],"components":{"securitySchemes":{"apiKeyAuth":{"type":"apiKey","in":"header","name":"x-api-key","description":"Required for /ohlcv and /trade endpoints.\nPass your API key in the x-api-key header.\nPublic /market/* endpoints do not require authentication.\n"}}},"paths":{"/volatility-index":{"get":{"summary":"Get volatility index OHLC","description":"Returns OHLC (open / high / low / close) of an exchange's published\nvolatility index for a given underlying. Currently the primary\nsource is Deribit's DVOL (BTC and ETH).\n\nThis endpoint is limited to 10000 records per request.\n","tags":["market_data"],"parameters":[{"name":"exchange","in":"query","required":true,"description":"Exchange that publishes the index (e.g. `deribit`).","schema":{"type":"string"}},{"name":"market","in":"query","required":true,"description":"Underlying asset (e.g. `BTC`, `ETH`).","schema":{"type":"string"}},{"name":"start_datetime","in":"query","required":true,"description":"Window start. Accepts any format parseable by `parseDateTime64BestEffort`\n— e.g. a plain date (`2026-03-01`, treated as midnight UTC) or a full\nISO 8601 timestamp (`2026-03-01T12:00:00Z`).\n","schema":{"type":"string","format":"date-time"}},{"name":"end_datetime","in":"query","required":true,"description":"Window end. Same format as `start_datetime`.","schema":{"type":"string","format":"date-time"}}],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"type":"object","properties":{"time":{"type":"string","format":"date-time","description":"Bar timestamp (UTC)"},"exchange":{"type":"string","description":"Exchange that publishes the index"},"market":{"type":"string","description":"Underlying asset"},"open":{"type":"number","format":"decimal","description":"Open value of the index for the bar"},"high":{"type":"number","format":"decimal","description":"High value"},"low":{"type":"number","format":"decimal","description":"Low value"},"close":{"type":"number","format":"decimal","description":"Close value"}}}}}}}}}}}}
```


# Option chain

5-minute snapshots of every listed option across Deribit, Binance, OKX, and Bybit. One row per (exchange, instrument, snapshot) carrying mark / bid / ask prices, implied volatilities, greeks, open interest, and 24h volume.

> **History.** Chain snapshots are available from **2026-05-12** onward (all four exchanges). For option history before that date, use the option **trade** tape instead — `api.trade` with `exchange = 'deribit-option'` reaches back to 2016 (see [Coverage](/data/coverage)) — and recompute IV / greeks from the premium.

## SQL API

### `api.option_chains`

| Column             | Type               | Description                                                 |
| ------------------ | ------------------ | ----------------------------------------------------------- |
| `exchange`         | LowCardinality     | `deribit`, `binance`, `okx`, or `bybit`                     |
| `timestamp`        | DateTime64(9, UTC) | Snapshot time (5-minute cadence)                            |
| `instrument_name`  | String             | Exchange-native instrument id                               |
| `underlying_asset` | LowCardinality     | Underlying coin (e.g. `BTC`, `ETH`, `SOL`)                  |
| `quote_asset`      | LowCardinality     | Premium-denomination asset                                  |
| `expiration`       | DateTime64(9, UTC) | Expiry time                                                 |
| `strike`           | Decimal(38, 18)    | Strike price                                                |
| `option_type`      | Enum8('C','P')     | Call or Put                                                 |
| `bid_price`        | Decimal(38, 18)    | Best bid                                                    |
| `ask_price`        | Decimal(38, 18)    | Best ask                                                    |
| `last_price`       | Decimal(38, 18)    | Last trade                                                  |
| `mark_price`       | Decimal(38, 18)    | Exchange mark                                               |
| `index_price`      | Decimal(38, 18)    | Underlying index price                                      |
| `underlying_price` | Decimal(38, 18)    | Spot/forward of the underlying at snapshot time             |
| `mark_iv`          | Decimal(38, 18)    | Mark implied volatility, **annualized %** (e.g. 65.0 = 65%) |
| `bid_iv`           | Decimal(38, 18)    | Bid IV, annualized %                                        |
| `ask_iv`           | Decimal(38, 18)    | Ask IV, annualized %                                        |
| `delta`            | Decimal(38, 18)    | Delta                                                       |
| `gamma`            | Decimal(38, 18)    | Gamma                                                       |
| `vega`             | Decimal(38, 18)    | Vega                                                        |
| `theta`            | Decimal(38, 18)    | Theta                                                       |
| `open_interest`    | Decimal(38, 18)    | Open interest (contracts)                                   |
| `volume_24h`       | Decimal(38, 18)    | Trailing 24h volume                                         |
| `state`            | LowCardinality     | Exchange-reported state (`open`, `TRADING`, `live`, etc.)   |

> **Implied volatility unit.** All four exchanges return IV annualized; we normalize the scale at ingest so `mark_iv = 65.0` means 65% annualized vol regardless of source exchange.

### Examples

**1. Latest snapshot of the BTC option chain on Deribit:**

```sql
WITH (
  SELECT max(timestamp)
  FROM api.option_chains
  WHERE exchange = 'deribit' AND underlying_asset = 'BTC'
) AS latest_ts
SELECT *
FROM api.option_chains
WHERE exchange = 'deribit'
  AND underlying_asset = 'BTC'
  AND timestamp = latest_ts
ORDER BY expiration, strike, option_type
```

**2. ATM volatility smile for one expiration (any exchange):**

```sql
WITH (
  SELECT max(timestamp)
  FROM api.option_chains
  WHERE exchange = 'deribit' AND underlying_asset = 'BTC'
) AS latest_ts
SELECT strike, option_type, mark_iv, bid_iv, ask_iv
FROM api.option_chains
WHERE exchange = 'deribit'
  AND underlying_asset = 'BTC'
  AND toDate(expiration) = toDate('2026-06-26')
  AND timestamp = latest_ts
  AND mark_iv != 0
ORDER BY strike, option_type
```

**3. ATM-strike IV across exchanges (cross-venue arb signal):**

```sql
WITH (
  SELECT max(timestamp)
  FROM api.option_chains
  WHERE underlying_asset = 'BTC'
) AS latest_ts
SELECT exchange, instrument_name, mark_iv, mark_price
FROM api.option_chains
WHERE underlying_asset = 'BTC'
  AND option_type = 'C'
  AND timestamp = latest_ts
  AND underlying_price > 0
  AND abs(strike - underlying_price) / toFloat64(underlying_price) < 0.02
ORDER BY exchange, abs(strike - underlying_price)
LIMIT 8
```

## REST API

## Get Option Chain Snapshot

> Latest 5-minute snapshot of the full option chain for (exchange, underlying\_asset). Returns one row per listed instrument with prices, IVs, greeks, OI, and 24h volume.<br>

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[{"apiKeyAuth":[]}],"components":{"securitySchemes":{"apiKeyAuth":{"type":"apiKey","in":"header","name":"x-api-key","description":"Required for /ohlcv and /trade endpoints.\nPass your API key in the x-api-key header.\nPublic /market/* endpoints do not require authentication.\n"}},"schemas":{"OptionChainRow":{"type":"object","properties":{"instrument_name":{"type":"string","description":"Exchange-native instrument identifier"},"expiration":{"type":"string","format":"date","description":"Expiry date (UTC)"},"strike":{"type":"number","format":"decimal","description":"Strike price"},"option_type":{"type":"string","enum":["C","P"],"description":"Call or Put"},"mark_price":{"type":"number","format":"decimal","description":"Mark price (in underlying)"},"bid_price":{"type":"number","format":"decimal","description":"Best bid"},"ask_price":{"type":"number","format":"decimal","description":"Best ask"},"mark_iv":{"type":"number","format":"decimal","description":"Mark implied volatility (annualized %)"},"bid_iv":{"type":"number","format":"decimal","description":"Bid IV"},"ask_iv":{"type":"number","format":"decimal","description":"Ask IV"},"delta":{"type":"number","format":"decimal","description":"Delta"},"gamma":{"type":"number","format":"decimal","description":"Gamma"},"vega":{"type":"number","format":"decimal","description":"Vega"},"theta":{"type":"number","format":"decimal","description":"Theta"},"open_interest":{"type":"number","format":"decimal","description":"Open interest"},"volume_24h":{"type":"number","format":"decimal","description":"24h trading volume"},"underlying_price":{"type":"number","format":"decimal","description":"Spot/index price of the underlying at snapshot time"},"dte":{"type":"integer","description":"Days-to-expiry from snapshot timestamp"}}}}},"paths":{"/option/chain":{"get":{"summary":"Get Option Chain Snapshot","description":"Latest 5-minute snapshot of the full option chain for (exchange, underlying_asset). Returns one row per listed instrument with prices, IVs, greeks, OI, and 24h volume.\n","tags":["market_data"],"parameters":[{"name":"exchange","in":"query","required":true,"schema":{"type":"string","enum":["deribit","binance","okx","bybit"]},"description":"Exchange to query — one of `deribit`, `binance`, `okx`, `bybit`."},{"name":"underlying_asset","in":"query","required":true,"schema":{"type":"string"},"description":"Underlying coin (e.g. `BTC`, `ETH`, `SOL`)."},{"name":"expiration","in":"query","required":true,"schema":{"type":"string","format":"date"},"description":"Expiry date, ISO (YYYY-MM-DD). Required."}],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"$ref":"#/components/schemas/OptionChainRow"}}}}}}}}}}
```


# Option smile

IV-by-strike for a single expiration on the latest snapshot. Both option types returned, ordered by strike. Designed for direct plotting (`x = strike, y = mark_iv`) with optional bid/ask brackets.

The endpoint is a focused projection of [Option chain](/data/option-chain) — same underlying data, narrower column set, server-side filtered to non-zero IV.

## SQL API

The smile shape comes from the same `api.option_chains` table:

```sql
WITH (
  SELECT max(timestamp)
  FROM api.option_chains
  WHERE exchange = 'deribit' AND underlying_asset = 'BTC'
) AS latest_ts
SELECT strike, option_type, mark_iv, bid_iv, ask_iv, delta, underlying_price
FROM api.option_chains
WHERE exchange = 'deribit'
  AND underlying_asset = 'BTC'
  AND toDate(expiration) = toDate('2026-06-26')
  AND timestamp = latest_ts
  AND mark_iv != 0
ORDER BY strike, option_type
```

Plot the result as a scatter / line over `(strike, mark_iv)` to see the smile. For an OTM-only wing curve, filter to `option_type = 'C' AND strike > underlying_price` (calls above spot) and `option_type = 'P' AND strike < underlying_price` (puts below spot).

### Cross-expiration vol surface

To build a vol surface, query each expiration in turn (or join with `dte` in the result):

```sql
WITH (
  SELECT max(timestamp)
  FROM api.option_chains
  WHERE exchange = 'deribit' AND underlying_asset = 'BTC'
) AS latest_ts
SELECT
  toDate(expiration)                                AS expiry,
  strike / toFloat64(underlying_price)              AS moneyness,
  mark_iv                                           AS iv,
  toUInt32(date_diff('day', timestamp, expiration)) AS dte
FROM api.option_chains
WHERE exchange = 'deribit'
  AND underlying_asset = 'BTC'
  AND mark_iv != 0
  AND timestamp = latest_ts
ORDER BY expiry, moneyness
```

## REST API

## Get Volatility Smile

> IV-by-strike for a single expiration on the latest snapshot. Both option types (C/P) returned, ordered by strike. Designed for direct plotting (x=strike, y=mark\_iv).<br>

```json
{"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[{"apiKeyAuth":[]}],"components":{"securitySchemes":{"apiKeyAuth":{"type":"apiKey","in":"header","name":"x-api-key","description":"Required for /ohlcv and /trade endpoints.\nPass your API key in the x-api-key header.\nPublic /market/* endpoints do not require authentication.\n"}},"schemas":{"OptionSmileRow":{"type":"object","properties":{"strike":{"type":"number","format":"decimal","description":"Strike price"},"option_type":{"type":"string","enum":["C","P"],"description":"Call or Put"},"mark_iv":{"type":"number","format":"decimal","description":"Mark implied volatility (annualized %)"},"bid_iv":{"type":"number","format":"decimal","description":"Bid IV"},"ask_iv":{"type":"number","format":"decimal","description":"Ask IV"},"delta":{"type":"number","format":"decimal","description":"Delta"},"underlying_price":{"type":"number","format":"decimal","description":"Spot/index price of the underlying at snapshot time"},"dte":{"type":"integer","description":"Days-to-expiry"}}}}},"paths":{"/option/smile":{"get":{"summary":"Get Volatility Smile","description":"IV-by-strike for a single expiration on the latest snapshot. Both option types (C/P) returned, ordered by strike. Designed for direct plotting (x=strike, y=mark_iv).\n","tags":["market_data"],"parameters":[{"name":"exchange","in":"query","required":true,"schema":{"type":"string","enum":["deribit","binance","okx","bybit"]},"description":"Exchange — one of `deribit`, `binance`, `okx`, `bybit`."},{"name":"underlying_asset","in":"query","required":true,"schema":{"type":"string"},"description":"Underlying coin (e.g. `BTC`)."},{"name":"expiration","in":"query","required":true,"schema":{"type":"string","format":"date"},"description":"Expiry date, ISO (`YYYY-MM-DD`). Required."}],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"$ref":"#/components/schemas/OptionSmileRow"}}}}}}}}}}
```


# DBeaver / DataGrip / VS Code

Connect the Koinju ClickHouse database from a desktop SQL IDE — DBeaver, JetBrains DataGrip, or VS Code — and run your first query.

## Before you start

| Setting       | Value                     |
| ------------- | ------------------------- |
| Host          | the provided database URL |
| Port          | 8443                      |
| Protocol      | HTTPS / SSL enabled       |
| Database      | api                       |
| User/Password | provided by Koinju        |

Don't have credentials yet? See [How to connect](/how-to-connect).

Port `8443` is the ClickHouse **HTTPS interface** and is what every HTTP/JDBC-based driver below uses. `9440` is the alternative native-protocol-over-TLS port (used only by the native CLI client).

## Step-by-step

### DBeaver

1. **Database ▸ New Database Connection**.
2. Pick **ClickHouse** from the list, click **Next**.
3. On the **Main** tab set:
   * **Host**: the provided database URL
   * **Port**: `8443`
   * **Database/Schema**: `api`
   * **Username** / **Password**: provided by Koinju
4. Open the **SSL** tab and tick **Use SSL**.
5. Click **Test Connection** (DBeaver will offer to download the ClickHouse driver the first time — accept).
6. **Finish**.

### JetBrains DataGrip

1. **File ▸ New ▸ Data Source ▸ ClickHouse**. DataGrip auto-downloads the JDBC driver on first use.
2. Set the connection either by fields (Host = provided database URL, Port = `8443`, Database = `api`) or by pasting the URL directly:

   ```
   jdbc:clickhouse://<host>:8443/api?ssl=true
   ```
3. Enter the **User** and **Password** provided by Koinju.
4. Click **Test Connection**, then **OK**.

### VS Code

1. Install the [**SQLTools**](https://marketplace.visualstudio.com/items?itemName=mtxr.sqltools) extension and the [**SQLTools ClickHouse Driver**](https://marketplace.visualstudio.com/items?itemName=ultram4rine.sqltools-clickhouse-driver) from the VS Code Marketplace.
2. Add a new connection with:
   * **Host**: the provided database URL
   * **Port**: `8443`
   * **SSL**: `true`
   * **Database**: `api`
   * **User** / **Password**: provided by Koinju
3. Connect and open a new SQL editor against the connection.

## First sanity query

Run this in the IDE's SQL editor:

```sql
SELECT exchange, market, timestamp, price
FROM api.trade
WHERE market LIKE 'BTC%' AND timestamp > toStartOfDay(now())
ORDER BY timestamp DESC LIMIT 20
```

If you get \~20 rows back, you're connected. Next, explore what data is available in [Data](/data/coverage).


# Excel

Pull Koinju market data straight into Microsoft Excel using either the ClickHouse ODBC driver or Excel's built-in Power Query connector.

## Before you start

| Setting       | Value                     |
| ------------- | ------------------------- |
| Host          | the provided database URL |
| Port          | 8443                      |
| Protocol      | HTTPS / SSL enabled       |
| Database      | api                       |
| User/Password | provided by Koinju        |

Don't have credentials yet? See [How to connect](/how-to-connect).

## Step-by-step

### Option A — ODBC driver

1. Download the Windows ODBC driver MSI from the [ClickHouse ODBC releases page](https://github.com/ClickHouse/clickhouse-odbc/releases) and install it.
2. Open **ODBC Data Sources** (Windows) and create a new **DSN** for the ClickHouse driver:
   * **Host**: the provided database URL
   * **Port**: `8443`
   * **SSL**: on
   * **Database**: `api`
3. In Excel: **Data ▸ Get Data ▸ From Other Sources ▸ From ODBC**.
4. Pick the DSN you created, enter the **username** and **password** provided by Koinju.
5. Choose a table (or supply a SQL statement) and **Load**.

### Option B — Power Query connector

1. Make sure the ClickHouse ODBC driver from Option A is installed (the Power Query ClickHouse connector relies on it).
2. In Excel: **Data ▸ Get Data ▸ From Database ▸ ClickHouse**.
3. Enter:
   * **Server / Host**: the provided database URL, port `8443`
   * **SSL**: enabled
   * **Database**: `api`
4. Supply the **username** and **password** provided by Koinju and load the data.

{% hint style="info" %}
The ClickHouse ODBC driver connects over the **HTTP(S) interface on port 8443** — the same endpoint used by every other tool in this section.
{% endhint %}

If timezone-aware datetimes look shifted or misbehave once loaded into a worksheet, see the timezone guidance in [How to connect](/how-to-connect).

## First sanity query

When prompted for a SQL statement (Option A's "advanced" entry or a Power Query native query), run:

```sql
SELECT exchange, market, timestamp, price
FROM api.trade
WHERE market LIKE 'BTC%' AND timestamp > toStartOfDay(now())
ORDER BY timestamp DESC LIMIT 20
```

If you get \~20 rows back, you're connected. Next, explore what data is available in [Data](/data/coverage).


# Google Sheets

Query the Koinju market-data database from a Google Sheet using a small Apps Script custom function.

## Before you start

| Setting       | Value                     |
| ------------- | ------------------------- |
| Host          | the provided database URL |
| Port          | 8443                      |
| Protocol      | HTTPS / SSL enabled       |
| Database      | api                       |
| User/Password | provided by Koinju        |

Don't have credentials yet? See [How to connect](/how-to-connect).

Google Sheets has **no native ClickHouse connector**. The supported approach is a custom function backed by Apps Script, which calls the ClickHouse HTTPS interface for you.

## Step-by-step

1. In your sheet, open **Extensions ▸ Apps Script**.
2. Delete any boilerplate and paste the script below, then **Save**.
3. In the Apps Script editor, open **Project Settings ▸ Script Properties** and add three properties:
   * `CH_URL` — the provided database **host only** (no `https://`, no port)
   * `CH_USER` — the username provided by Koinju
   * `CH_PASS` — the password provided by Koinju
4. Back in the sheet, call the function from any cell, e.g.:

   ```
   =CHQUERY("SELECT exchange, market, price FROM api.trade WHERE market LIKE 'BTC%' AND timestamp > toStartOfDay(now()) ORDER BY timestamp DESC LIMIT 20")
   ```

```javascript
/**
 * Koinju ClickHouse → Google Sheets.
 * Script Properties: CH_URL (host only), CH_USER, CH_PASS.
 * Cell usage: =CHQUERY("SELECT exchange,market,price FROM api.trade WHERE market LIKE 'BTC%' AND timestamp>toStartOfDay(now()) ORDER BY timestamp DESC LIMIT 20")
 */
function CHQUERY(sql) {
  if (!sql) throw new Error('Pass a SQL string');
  var p = PropertiesService.getScriptProperties();
  var host = p.getProperty('CH_URL'), user = p.getProperty('CH_USER'), pass = p.getProperty('CH_PASS');
  if (!host || !user || !pass) throw new Error('Set CH_URL / CH_USER / CH_PASS in Script Properties');
  var q = /format\s+\w+\s*;?\s*$/i.test(sql) ? sql : sql.replace(/;?\s*$/, '') + ' FORMAT TabSeparatedWithNames';
  var url = 'https://' + host + ':8443/?database=api';
  var res = UrlFetchApp.fetch(url, {
    method: 'post', payload: q,
    headers: { Authorization: 'Basic ' + Utilities.base64Encode(user + ':' + pass) },
    muteHttpExceptions: true
  });
  var code = res.getResponseCode(), body = res.getContentText();
  if (code !== 200) throw new Error('ClickHouse ' + code + ': ' + body);
  return body.replace(/\n$/, '').split('\n').map(function (line) { return line.split('\t'); });
}
```

How it works:

* It calls the ClickHouse **HTTPS interface on port 8443** with **HTTP Basic auth** built from your Script Properties.
* `FORMAT TabSeparatedWithNames` is auto-appended (unless your SQL already ends in a `FORMAT` clause), so the response is parsed into a 2D array that **spills into a sheet range** with a header row.
* Credentials live in **Script Properties, never in cells** — nobody viewing the sheet sees them.
* Apps Script enforces a **\~6-minute execution limit** and **response-size limits**, so always filter by time and add a `LIMIT` to keep result sets small.

## First sanity query

In any cell:

```
=CHQUERY("SELECT exchange, market, timestamp, price FROM api.trade WHERE market LIKE 'BTC%' AND timestamp > toStartOfDay(now()) ORDER BY timestamp DESC LIMIT 20")
```

If \~20 rows spill into the sheet, you're connected. Next, explore what data is available in [Data](/data/coverage).


# ClickHouse CLI & BI tools

Connect from the `clickhouse-client` command line, plain `curl`, or any major BI tool.

## Before you start

| Setting       | Value                     |
| ------------- | ------------------------- |
| Host          | the provided database URL |
| Port          | 8443                      |
| Protocol      | HTTPS / SSL enabled       |
| Database      | api                       |
| User/Password | provided by Koinju        |

Don't have credentials yet? See [How to connect](/how-to-connect).

## Step-by-step

### Native CLI — `clickhouse-client`

The native client uses the TLS native protocol on port `9440`:

```sh
clickhouse-client \
  --host <host> \
  --port 9440 \
  --secure \
  --user <user> \
  --password \
  --database api
```

`--password` with no value prompts interactively. Once connected you get an interactive SQL shell.

### HTTP CLI — `curl`

The HTTPS interface on port `8443` accepts a query as the POST body with HTTP Basic auth:

```sh
curl --user <user>:<pass> \
  'https://<host>:8443/?database=api' \
  --data-binary "SELECT exchange, market, timestamp, price FROM api.trade WHERE market LIKE 'BTC%' AND timestamp > toStartOfDay(now()) ORDER BY timestamp DESC LIMIT 20"
```

### BI tools

Tableau, Metabase, Superset, Power BI and similar tools all connect to the **same HTTPS endpoint on port 8443** (host = the provided database URL, database = `api`, SSL enabled, user/password provided by Koinju). ClickHouse maintains official, tool-by-tool setup guides — follow them and plug in the connection facts above: [ClickHouse data-visualization integrations](https://clickhouse.com/docs/integrations/data-visualization).

## First sanity query

```sql
SELECT exchange, market, timestamp, price
FROM api.trade
WHERE market LIKE 'BTC%' AND timestamp > toStartOfDay(now())
ORDER BY timestamp DESC LIMIT 20
```

If you get \~20 rows back, you're connected. Next, explore what data is available in [Data](/data/coverage).


# Introduction

Utilizing server-side computing can be particularly beneficial when processing indicators or backtesting with large datasets that can't fit on a user's disk or be loaded into RAM with conventional tools like pandas. This approach minimizes compute time on standard computers. With full access to public data tables in the database, users can not only retrieve market data but also execute computations directly on the server, harnessing the speed of our Clickhouse instance

### Example: Computing the sum, the average and the standard deviation of the quantity or ALL the orders done on BTC-USD on Coinbase from 2014.

<figure><img src="/files/S7nx0sWh90MlXFjNCJqh" alt=""><figcaption></figcaption></figure>

This computation took 26s on our server and it's easy to see that this is handier than have to download a 835 millions rows long CSV file.

{% hint style="danger" %}
The users have limits for how many rows their query can read depending on their tier, this information is provided to you by Koinju and an error will be thrown by the database if you run queries that would go above the limit.
{% endhint %}

In the next chapter we will show how to run a backtest for a simple SMA strategy in seconds direcly inside the database.


# Backtesting simple SMA strategy

This doc will explain how to run a backtest for a simple SMA (*simple moving average*) crossing strategy between 50 and 200 period SMA's directly inside the Clickhouse instance.\
For simplicity, we assume that there is no transaction cost, slippage or financing cost thus the returns obtained are unrealistic.

We will present it step by step, then we will provide the full code at the end.

## Select the data

We are selecting the 1 minutes candles for `BTC-USD` from `coinbase` for the last 12 months.

```sql
with now() - interval 12 month as start_date, toStartOfMinute(now()) as end_date, 10000 as initial_cash,
    -- Fetching BTC data for the specified period
    btc_data as (SELECT timestamp,
                        argMaxMerge(close) as close
                 FROM public_data.candle_1m
                 WHERE market = 'BTC-USD'
                   AND exchange = 'coinbase'
                   and timestamp > start_date
                   and timestamp < end_date
                 GROUP BY timestamp)
```

Functions used: [`argMaxMerge`](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-merge), [`toStartOfMinute`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofminute), [`lagInFrame`](https://clickhouse.com/docs/sql-reference/window-functions#laginframe), [`row_number`](https://clickhouse.com/docs/sql-reference/window-functions#row_number), [`stddevPop`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevpop), [`toStartOfDay`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofday), [`sqrt`](https://clickhouse.com/docs/sql-reference/functions/math-functions#sqrt)

`btc_data` contains:

| timestamp         | close    |
| ----------------- | -------- |
| 2025-03-07T08:50Z | 88409.84 |
| 2024-07-07T09:24Z | 57631.88 |
| 2024-10-29T23:04Z | 72649.13 |
| ...               | ...      |

## Compute the SMA

The 50 and 200 period SMA is computed using a window function.

Clickhouse offers a wide range of statistical functions documented [here](https://clickhouse.com/docs/sql-reference/window-functions).

```sql
--- previous code snippet ---
    sma AS (SELECT timestamp,
                   close,
                   avg(close) OVER w50  AS sma50,
                   avg(close) OVER w200 AS sma200
            FROM btc_data
            -- using -1 to avoid the current row in the moving average calculation and thus avoid lookahead bias
            WINDOW w50 AS (ORDER BY timestamp ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING ),
                   w200 AS (ORDER BY timestamp ROWS BETWEEN 200 PRECEDING AND 1 PRECEDING )),
```

`sma` contains:

| timestamp         | close    | sma50             | sma200            |
| ----------------- | -------- | ----------------- | ----------------- |
| 2024-06-11T15:23Z | 66908.00 | 66918.2           | 66918.2           |
| 2024-06-11T15:24Z | 66870.67 | 66913.1           | 66913.1           |
| 2024-06-11T15:25Z | 66835.35 | 66898.95666666667 | 66898.95666666667 |
| 2024-06-11T15:26Z | 66757.10 | 66883.055         | 66883.055         |
| 2024-06-11T15:27Z | 66779.25 | 66857.864         | 66857.864         |
| 2024-06-11T15:28Z | 66795.82 | 66844.76166666666 | 66844.76166666666 |
| 2024-06-11T15:29Z | 66817.05 | 66837.76999999999 | 66837.76999999999 |
| 2024-06-11T15:30Z | 66776.63 | 66835.18000000001 | 66835.18000000001 |
| 2024-06-11T15:31Z | 66780.80 | 66828.67444444445 | 66828.67444444445 |
| ...               | ...      | ...               | ...               |

## Generate the signals and extract the trades

We detect the crossing of the two SMA's and generate the signals accordingly.

```sql
    --- previous code snippet ---
    signals AS (SELECT timestamp,
                          close,
                          sma50,
                          sma200,
                          CASE
                              WHEN sma50 > sma200 AND lagInFrame(sma50) OVER w < lagInFrame(sma200) OVER w THEN 'long' -- golden cross
                              WHEN sma50 < sma200 AND lagInFrame(sma50) OVER w > lagInFrame(sma200) OVER w THEN 'short' -- death cross
                              ELSE NULL
                              END AS signal
                   FROM sma
                   WINDOW w AS (ORDER BY timestamp)),
    trades AS (SELECT timestamp,
                      close                                  AS price,
                      signal,
                      row_number() OVER (ORDER BY timestamp) AS trade_id
               FROM signals
               WHERE signal is not null)
```

`trades` contains:

| timestamp         | price    | signal | trade\_id |
| ----------------- | -------- | ------ | --------- |
| 2024-06-11T17:29Z | 66258.98 | short  | 1         |
| 2024-06-11T18:03Z | 66784.28 | long   | 2         |
| 2024-06-12T00:10Z | 67354.27 | short  | 3         |
| ...               | ...      | ...    | ...       |

## Compute strategy performance

We regroup the trades by their signal and ids to present the data with entry and exit prices for each trade for the PnL calculation.

```sql
    --- previous code snippet ---
    returns AS (SELECT entry.trade_id,
                           entry.timestamp          AS entry_date,
                           exit.timestamp           AS exit_date,
                           entry.price              AS entry_price,
                           exit.price               AS exit_price,
                           entry.signal             as signal,
                           if(signal = 'long', exit_price / entry_price, entry_price / exit_price) - 1  as trade_return
                    FROM trades entry
                             LEFT JOIN trades exit ON entry.trade_id + 1 = exit.trade_id
                    where exit_price != 0 -- ignoring the last unclosed trade
                    order by trade_id)
```

`returns` contains:

| trade\_id | entry\_date       | exit\_date        | entry\_price | exit\_price | signal | trade\_return           |
| --------- | ----------------- | ----------------- | ------------ | ----------- | ------ | ----------------------- |
| 1         | 2024-06-11T17:22Z | 2024-06-11T18:11Z | 66289.25     | 66695.99    | short  | -0.00609841761101379559 |
| 2         | 2024-06-11T18:11Z | 2024-06-12T00:10Z | 66695.99     | 67354.27    | long   | 0.00986985874263205329  |
| 3         | 2024-06-12T00:10Z | 2024-06-12T02:32Z | 67354.27     | 67455.74    | short  | -0.00150424559866958691 |
| 4         | 2024-06-12T02:32Z | 2024-06-12T05:33Z | 67455.74     | 67362.65    | long   | -0.00138001599270870055 |
| 5         | 2024-06-12T05:33Z | 2024-06-12T07:57Z | 67362.65     | 67455.94    | short  | -0.00138297679937452507 |
| 6         | 2024-06-12T07:57Z | 2024-06-12T16:33Z | 67455.94     | 69424.35    | long   | 0.02918067704638020017  |
| 7         | 2024-06-12T16:33Z | 2024-06-12T21:51Z | 69424.35     | 68534.74    | short  | 0.01298042423448312490  |
| 8         | 2024-06-12T21:51Z | 2024-06-12T23:30Z | 68534.74     | 68173.60    | long   | -0.00526944437229936234 |
| ...       | ...               | ...               | ...          | ...         | ...    | ...                     |

### Sharpe Ratio

We compute the Sharpe ratio of the strategy using the returns from the trades.

```sql
    --- previous code snippet ---
    SELECT  avg(trade_return) / stddevPop(trade_return)   AS sharpe_ratio from returns
```

outputs `0.022227898711626174`

The above results can be also grouped by day and the Sharpe ratio can then be annualised to get a better understanding of the strategy performance over time.

```sql
    --- previous code snippet ---
    daily_retuns AS (
    SELECT  sum( trade_return) AS daily_return
    from returns
    group by toStartOfDay(exit_date)
    )
    SELECT  sqrt(365) *  avg(daily_return) / stddevPop(daily_return)   AS sharpe_ratio from daily_retuns;
```

outputs `1.3117539455990057`

## Full example

```sql
with now() - interval 12 month as start_date, toStartOfMinute(now()) as end_date,
    -- Fetching BTC data for the specified period
    btc_data as (SELECT timestamp,
                        argMaxMerge(close) as close
                 FROM public_data.candle_1m
                 WHERE market = 'BTC-USD'
                   AND exchange = 'coinbase'
                   and timestamp > start_date
                   and timestamp < end_date
                 GROUP BY timestamp),
    -- Calculating 50 and 200 period simple moving averages
    sma AS (SELECT timestamp,
                   close,
                   avg(close) OVER w50  AS sma50,
                   avg(close) OVER w200 AS sma200
            FROM btc_data
            -- using -1 to avoid the current row in the moving average calculation and thus avoid lookahead bias
            WINDOW w50 AS (ORDER BY timestamp ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING ),
                   w200 AS (ORDER BY timestamp ROWS BETWEEN 200 PRECEDING AND 1 PRECEDING )),
    signals AS (SELECT timestamp,
                       close,
                       sma50,
                       sma200,
                       CASE
                           WHEN sma50 > sma200 AND lagInFrame(sma50) OVER w < lagInFrame(sma200) OVER w
                               THEN 'long' -- golden cross
                           WHEN sma50 < sma200 AND lagInFrame(sma50) OVER w > lagInFrame(sma200) OVER w
                               THEN 'short' -- death cross
                           END AS signal
                FROM sma
                WINDOW w AS (ORDER BY timestamp)),
    trades AS (SELECT timestamp,
                      close                                  AS price,
                      signal,
                      row_number() OVER (ORDER BY timestamp) AS trade_id
               FROM signals
               WHERE signal is not null),
    returns AS (SELECT entry.trade_id,
                       entry.timestamp                                                             AS entry_date,
                       exit.timestamp                                                              AS exit_date,
                       entry.price                                                                 AS entry_price,
                       exit.price                                                                  AS exit_price,
                       entry.signal                                                                as signal,
                       if(signal = 'long', exit_price / entry_price, entry_price / exit_price) - 1 as trade_return
                FROM trades entry
                         LEFT JOIN trades exit ON entry.trade_id + 1 = exit.trade_id
                where exit_price != 0 -- ignoring the last unclosed trade
                order by trade_id),
    daily_retuns AS (SELECT sum(trade_return) AS daily_return
                     from returns
                     group by toStartOfDay(exit_date))
SELECT sqrt(365) * avg(daily_return) / stddevPop(daily_return) AS sharpe_ratio
from daily_retuns;
```

The execution speed of this backtest is rapid, even for large datasets.

| number of candles     | Execution time |
| --------------------- | -------------- |
| 525,525 ( 1 Year)     | 1 s 678 ms     |
| 2,628,415 ( 5 Years ) | 7 s 315 ms     |

The dataset can always be downloaded from the table by running the `btc_data` sub-query.


# Historical volatility

This page shows how to compute the rolling 30-day **realised volatility** of a crypto pair entirely server-side, using two of the most common estimators:

* **Close-to-close** — the textbook approach: standard deviation of daily log returns, annualised by `√365`.
* **Parkinson** — uses the daily range (high/low) instead of just closes. Lower variance estimator because it sees more of the intraday move.

The full pipeline runs in a single SQL query against `api.ohlcv(...)`. We walk through it CTE-by-CTE then provide the full code at the end. The example is pinned to `2024-01-01` → `2024-12-31` to match the parity-tested baseline; for live data swap the two date literals for `now() - interval 13 month` and `toStartOfDay(now())`.

## Select the data

We pull daily candles for `BTC-USDT` on `binance` for the pinned window, with named bindings for the date range, window length, and annualisation factor.

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    30 AS window_days,
    365 AS sessions_per_year,
    candles AS (
        SELECT toDate(start) AS day, open, high, low, close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market = 'BTC-USDT'
          AND start >= start_date
          AND start <= end_date
    )
```

Functions used: [`toDate`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#todate), [`ln`](https://clickhouse.com/docs/sql-reference/functions/math-functions#ln), [`nullIf`](https://clickhouse.com/docs/sql-reference/functions/conditional-functions#nullif), [`lagInFrame`](https://clickhouse.com/docs/sql-reference/window-functions#laginframe), [`pow`](https://clickhouse.com/docs/sql-reference/functions/math-functions#pow), [`stddevSamp`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevsamp), [`sqrt`](https://clickhouse.com/docs/sql-reference/functions/math-functions#sqrt)

`candles` contains:

| day        | open     | high     | low      | close    |
| ---------- | -------- | -------- | -------- | -------- |
| 2024-01-01 | 42283.58 | 44184.10 | 42180.77 | 44179.55 |
| 2024-01-02 | 44179.55 | 45879.63 | 44148.34 | 44946.91 |
| 2024-01-03 | 44946.91 | 45500.00 | 40750.00 | 42845.23 |
| 2024-01-04 | 42845.23 | 44729.58 | 42613.77 | 44151.10 |
| ...        | ...      | ...      | ...      | ...      |

## Compute log returns and intraday range

Two per-row quantities feed the two estimators:

* `log_return = ln(close / close[t-1])` — close-to-close log return.
* `hl_log_sq = ln(high / low)²` — squared log range, the input to Parkinson.

We use `nullIf(..., 0)` on the lagged close so the very first row (no predecessor) yields `NULL` rather than tripping a division-by-zero on `Decimal(76, 20)`.

```sql
    returns AS (
        SELECT day, high, low, close,
               ln(close / nullIf(lagInFrame(close, 1) OVER (ORDER BY day), 0)) AS log_return,
               pow(ln(high / low), 2) AS hl_log_sq
        FROM candles
    )
```

`returns` contains:

| day        | close    | log\_return | hl\_log\_sq |
| ---------- | -------- | ----------- | ----------- |
| 2024-01-01 | 44179.55 | NULL        | 0.002153    |
| 2024-01-02 | 44946.91 | 0.017220    | 0.001480    |
| 2024-01-03 | 42845.23 | -0.047888   | 0.012156    |
| 2024-01-04 | 44151.10 | 0.030024    | 0.002348    |
| ...        | ...      | ...         | ...         |

## Roll the 30-day window

ClickHouse window functions compute both estimators in one pass:

* Close-to-close: `stddevSamp(log_return)` over the 30-row window, annualised by `√365` and reported in percent.
* Parkinson: `√( Σ ln(H/L)² / (4·N·ln 2) )` over the same window, also annualised.

We carry `count(log_return) OVER w` so we can drop early rows where the window isn't yet full (the first 30 days, before we have 30 valid log returns).

```sql
    vols AS (
        SELECT day,
               stddevSamp(log_return) OVER w * sqrt(sessions_per_year) * 100 AS vol_30d_cc_pct,
               sqrt(sum(hl_log_sq) OVER w / (4 * window_days * ln(2))) * sqrt(sessions_per_year) * 100 AS vol_30d_park_pct,
               count(log_return) OVER w AS valid_returns
        FROM returns
        WINDOW w AS (ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
    )
SELECT day, vol_30d_cc_pct, vol_30d_park_pct
FROM vols
WHERE valid_returns = window_days
ORDER BY day
```

Output:

| day        | vol\_30d\_cc\_pct | vol\_30d\_park\_pct |
| ---------- | ----------------- | ------------------- |
| 2024-01-31 | 53.90             | 58.88               |
| 2024-02-01 | 53.70             | 58.73               |
| 2024-02-02 | 51.02             | 54.19               |
| ...        | ...               | ...                 |
| 2024-12-29 | 44.39             | 56.48               |
| 2024-12-30 | 44.37             | 56.94               |
| 2024-12-31 | 44.38             | 57.53               |

## Notes on the estimators

* `stddevSamp` (Bessel-corrected, `ddof=1`) matches the convention used by most published HV charts — including Deribit's reference implementation and `pandas.rolling.std()` with default arguments. Use `stddevPop` if you prefer the population estimator.
* `√365` annualisation is appropriate for crypto (24/7 trading). For traditional markets, swap in `√252`.
* The Parkinson constant `1 / (4·N·ln 2)` ≈ `0.3607 / N` — switch the window length only by changing the `window_days` binding.

## Full example

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    30 AS window_days,
    365 AS sessions_per_year,
    candles AS (
        SELECT toDate(start) AS day, open, high, low, close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market = 'BTC-USDT'
          AND start >= start_date
          AND start <= end_date
    ),
    returns AS (
        SELECT day, high, low, close,
               ln(close / nullIf(lagInFrame(close, 1) OVER (ORDER BY day), 0)) AS log_return,
               pow(ln(high / low), 2) AS hl_log_sq
        FROM candles
    ),
    vols AS (
        SELECT day,
               stddevSamp(log_return) OVER w * sqrt(sessions_per_year) * 100 AS vol_30d_cc_pct,
               sqrt(sum(hl_log_sq) OVER w / (4 * window_days * ln(2))) * sqrt(sessions_per_year) * 100 AS vol_30d_park_pct,
               count(log_return) OVER w AS valid_returns
        FROM returns
        WINDOW w AS (ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
    )
SELECT day, vol_30d_cc_pct, vol_30d_park_pct
FROM vols
WHERE valid_returns = window_days
ORDER BY day
```

The whole pipeline runs in well under a second of compute time; round-trip latency dominates.

| window length | execution time |
| ------------- | -------------- |
| 1 year        | \~ 5–8 s       |
| 5 years       | \~ 7–10 s      |

The dataset is downloadable as-is by running the `candles` sub-query if you prefer to compute the volatilities client-side (see the companion [Python reference](https://gitlab.com/koinju/connector/exporter/-/blob/master/api/python_vs_sql/historical_volatility/original.py) for a reproducible parity baseline).

## See also

* [Backtesting a simple SMA strategy](/sql-cookbook/backtesting-simple-sma-strategy) — uses the same `api.ohlcv(...)` view and `lagInFrame` window pattern.
* Source article that inspired this example: [Historical volatility calculations (Deribit Insights)](https://insights.deribit.com/dev-hub/historical-volatility-calculations-python-code/).


# Sortino ratio

The Sortino ratio is a downside-aware variant of the Sharpe ratio: instead of the std of *all* returns in the denominator, it uses the std of just the *negative* ones — punishing downside volatility specifically.

Siblings: [Sharpe](/sql-cookbook/backtesting-simple-sma-strategy#sharpe-ratio) (in the SMA backtest) and [Maximum drawdown & Calmar](/sql-cookbook/max-drawdown-calmar).

## Query

We rank the five top crypto majors plus an equal-weight portfolio in a single CTE chain, computing daily simple returns from `api.ohlcv(...)` and applying

```
sortino = (E[r] · 365 − 0.01) / (σ_neg · √365)
```

over the full year, with a 1 % annualised risk-free rate (`0.01`) in the numerator. `r` = `(close − close[t-1]) / close[t-1]`, `σ_neg` = sample std (`stddevSamp`) of just the negative subset.

The example is pinned to `2024-01-01` → `2024-12-31` to match the parity-tested baseline; for live data swap the two date literals for `now() - interval 12 month` and `toStartOfDay(now())`.

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    365 AS sessions_per_year,
    0.01 AS risk_free_rate,
    candles AS (
        SELECT toDate(start) AS day, market, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market IN ('BTC-USDT', 'ETH-USDT', 'SOL-USDT', 'ADA-USDT', 'DOGE-USDT')
          AND start BETWEEN start_date AND end_date
    ),
    market_returns AS (
        SELECT day, market,
               close / lagInFrame(toNullable(close), 1) OVER (PARTITION BY market ORDER BY day) - 1 AS ret
        FROM candles
    ),
    all_returns AS (
        SELECT day, market, ret FROM market_returns WHERE ret IS NOT NULL
        UNION ALL
        SELECT day, 'PORTFOLIO' AS market, avg(ret) AS portfolio_ret
        FROM market_returns WHERE ret IS NOT NULL GROUP BY day
    )
SELECT market,
       (avg(ret) * sessions_per_year - risk_free_rate)
       / (stddevSamp(if(ret < 0, ret, NULL)) * sqrt(sessions_per_year)) AS sortino
FROM all_returns
GROUP BY market
ORDER BY market
```

Functions used: [`lagInFrame`](https://clickhouse.com/docs/sql-reference/window-functions#laginframe), [`toNullable`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tonullable), [`stddevSamp`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevsamp), [`if`](https://clickhouse.com/docs/sql-reference/functions/conditional-functions#if)

## Output (2024)

| market    | sortino |
| --------- | ------- |
| DOGE-USDT | 3.0529  |
| BTC-USDT  | 2.7336  |
| PORTFOLIO | 2.2168  |
| SOL-USDT  | 1.8328  |
| ETH-USDT  | 1.3739  |
| ADA-USDT  | 1.2769  |

The portfolio's `2.22` lands between BTC and SOL — diversification smooths the downside-only variance.

Bump `risk_free_rate` to whichever annualised reference you prefer (e.g. `0.04` for \~4 % T-bill, `0` to drop the term entirely).


# Maximum drawdown & Calmar ratio

**Maximum drawdown** is the largest peak-to-trough loss in a price series. The **Calmar ratio** divides the annualised return by `|max_drawdown|`, the natural sibling to the [Sharpe](/sql-cookbook/backtesting-simple-sma-strategy#sharpe-ratio) and [Sortino](/sql-cookbook/sortino-ratio) ratios — same numerator family, different risk denominator.

## Query

The interesting SQL pattern is the **running peak** — [`max(cum_return)`](https://clickhouse.com/docs/sql-reference/window-functions) over an `UNBOUNDED PRECEDING` frame, equivalent to pandas' `.expanding().max()`. We also lean on `∏(1 + r) ≡ exp(Σ log(1 + r))` because ClickHouse has no `cumprod`.

The example is pinned to `2024-01-01` → `2024-12-31` to match the parity-tested baseline; for live data swap the two date literals for `now() - interval 12 month` and `toStartOfDay(now())`.

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    365 AS sessions_per_year,
    candles AS (
        SELECT toDate(start) AS day, market, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market IN ('BTC-USDT', 'ETH-USDT', 'SOL-USDT', 'ADA-USDT', 'DOGE-USDT')
          AND start BETWEEN start_date AND end_date
    ),
    market_returns AS (
        SELECT day, market,
               close / lagInFrame(toNullable(close), 1) OVER (PARTITION BY market ORDER BY day) - 1 AS ret
        FROM candles
    ),
    all_returns AS (
        SELECT day, market, ret FROM market_returns WHERE ret IS NOT NULL
        UNION ALL
        SELECT day, 'PORTFOLIO' AS market, avg(ret) AS portfolio_ret
        FROM market_returns WHERE ret IS NOT NULL GROUP BY day
    ),
    cumulative AS (
        SELECT market, day, ret, exp(sum(log(1 + ret)) OVER w) AS cum_return
        FROM all_returns
        WINDOW w AS (PARTITION BY market ORDER BY day
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    ),
    drawdowns AS (
        SELECT market, ret, cum_return / max(cum_return) OVER w - 1 AS drawdown
        FROM cumulative
        WINDOW w AS (PARTITION BY market ORDER BY day
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    )
SELECT market,
       min(drawdown) AS max_drawdown,
       avg(ret) * sessions_per_year / abs(min(drawdown)) AS calmar
FROM drawdowns
GROUP BY market
ORDER BY market
```

Functions used: [`lagInFrame`](https://clickhouse.com/docs/sql-reference/window-functions#laginframe), [`toNullable`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tonullable), [`exp`](https://clickhouse.com/docs/sql-reference/functions/math-functions#exp), [`log`](https://clickhouse.com/docs/sql-reference/functions/math-functions#log), [`abs`](https://clickhouse.com/docs/sql-reference/functions/arithmetic-functions#abs)

The first three CTEs (`candles`, `market_returns`, `all_returns`) mirror the [Sortino](/sql-cookbook/sortino-ratio#query) page — fetch closes, cast to `Float64`, compute simple per-market returns with the `lagInFrame(toNullable(close), 1)` trick to get `NULL` (not `0`) on the first row of each partition, then synthesise the equal-weight `PORTFOLIO` row directly inside the `UNION ALL`.

The two new CTEs are where the work happens:

* `cumulative.cum_return` — running compounded growth. `exp(sum(log(1 + ret)))` over an unbounded preceding frame is mathematically identical to a cumulative product and numerically stable. ClickHouse has no `cumprod` window aggregate (Postgres, DuckDB, BigQuery don't either) — the log-sum-exp form is the category-wide workaround.
* `drawdowns.drawdown` — current `cum_return` over its running peak, minus one. Always `≤ 0` by construction; `min(drawdown)` per market is the max-drawdown.

## Output (2024)

For `BTC-USDT`, the rolling values look like:

| day        | cum\_return | drawdown |
| ---------- | ----------- | -------- |
| 2024-01-02 | 1.01737     | 0.00000  |
| 2024-03-13 | 1.71117     | 0.00000  |
| 2024-08-05 | 1.27148     | -0.25696 |
| 2024-12-31 | 2.20847     | 0.00000  |

A unit of starting capital ended at \~2.21× by year-end (the +120 % BTC year of 2024). The deepest drawdown was \~26 % from the March peak.

Final ranking:

| market    | max\_drawdown | calmar |
| --------- | ------------- | ------ |
| BTC-USDT  | -0.2615       | 3.4029 |
| DOGE-USDT | -0.5794       | 3.0152 |
| SOL-USDT  | -0.3823       | 2.2981 |
| PORTFOLIO | -0.4207       | 2.2469 |
| ETH-USDT  | -0.4526       | 1.2305 |
| ADA-USDT  | -0.5978       | 1.0943 |

BTC tops Calmar despite a smaller annualised return than DOGE — its drawdown is by far the shallowest, so per-unit-of-tail-risk it's the strongest.

| basket size × period | execution time |
| -------------------- | -------------- |
| 5 markets × 1 year   | \~20–30 s      |

Most of the time is in the two `UNBOUNDED PRECEDING` windows. For a single-asset Calmar, drop the `'PORTFOLIO'` half of `all_returns` and the query collapses further.


# Correlation

A pairwise **Pearson correlation matrix** of daily simple returns, across a basket of crypto majors, in one query.

## Query

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    candles AS (
        SELECT toDate(start) AS day, market, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market IN ('BTC-USDT', 'ETH-USDT', 'SOL-USDT', 'ADA-USDT', 'DOGE-USDT')
          AND start BETWEEN start_date AND end_date
    ),
    market_returns AS (
        SELECT day, market,
               close / lagInFrame(toNullable(close), 1) OVER (PARTITION BY market ORDER BY day) - 1 AS ret
        FROM candles
    )
SELECT a.market AS market_a, b.market AS market_b,
       corr(a.ret, b.ret) AS correlation
FROM market_returns a
JOIN market_returns b ON a.day = b.day
WHERE a.ret IS NOT NULL AND b.ret IS NOT NULL
GROUP BY market_a, market_b
ORDER BY market_a, market_b
```

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), [`lagInFrame`](https://clickhouse.com/docs/sql-reference/window-functions#laginframe), [`toNullable`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tonullable)

The `JOIN market_returns a JOIN market_returns b ON a.day = b.day` plus `WHERE a.ret IS NOT NULL AND b.ret IS NOT NULL` is exactly pandas' pairwise-complete behaviour — only days where both markets returned a valid number contribute to the correlation. ClickHouse's [`corr()`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/corr) is the standard Pearson coefficient `Σ(xᵢ − x̄)(yᵢ − ȳ) / √(Σ(xᵢ − x̄)² · Σ(yᵢ − ȳ)²)`; the `n−1` / `n` factors cancel out, so sample-vs-population doesn't apply.

## Output (2024)

The query above returns the long-form `(market_a, market_b, correlation)` triple — 25 rows for the 5 × 5 matrix, including the symmetric duplicates and the diagonal `1.0`s. For a triangular view, append `AND a.market < b.market` to the final `WHERE` clause.

|          | ADA-USDT | BTC-USDT | DOGE-USDT | ETH-USDT | SOL-USDT |
| -------- | -------- | -------- | --------- | -------- | -------- |
| **ADA**  | 1.00     | 0.66     | 0.65      | 0.66     | 0.63     |
| **BTC**  | 0.66     | 1.00     | 0.78      | 0.79     | 0.74     |
| **DOGE** | 0.65     | 0.78     | 1.00      | 0.66     | 0.63     |
| **ETH**  | 0.66     | 0.79     | 0.66      | 1.00     | 0.69     |
| **SOL**  | 0.63     | 0.74     | 0.63      | 0.69     | 1.00     |

Top large-cap pairs (BTC↔ETH at 0.79, BTC↔DOGE at 0.78, BTC↔SOL at 0.74) confirm crypto's well-known **single-factor regime** — BTC direction explains most of the daily variance across the rest of the market. ADA is the loosest leader-follower at the bottom of the ranking (\~0.63–0.66 with the others).

## Pivot to a matrix in SQL

ClickHouse has no native `PIVOT` clause, but the **conditional-aggregate** idiom (`anyIf`, `maxIf`, `sumIf`, …) produces a wide matrix in one extra wrapping query — no client-side reshape needed:

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    candles AS (
        SELECT toDate(start) AS day, market, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market IN ('BTC-USDT', 'ETH-USDT', 'SOL-USDT', 'ADA-USDT', 'DOGE-USDT')
          AND start BETWEEN start_date AND end_date
    ),
    market_returns AS (
        SELECT day, market,
               close / lagInFrame(toNullable(close), 1) OVER (PARTITION BY market ORDER BY day) - 1 AS ret
        FROM candles
    ),
    pairs AS (
        SELECT a.market AS market_a, b.market AS market_b,
               corr(a.ret, b.ret) AS correlation
        FROM market_returns a
        JOIN market_returns b ON a.day = b.day
        WHERE a.ret IS NOT NULL AND b.ret IS NOT NULL
        GROUP BY market_a, market_b
    )
SELECT market_a,
       mapFromArrays(groupArray(market_b), groupArray(correlation)) AS correlations
FROM pairs
GROUP BY market_a
ORDER BY market_a
```

Functions used: [`mapFromArrays`](https://clickhouse.com/docs/sql-reference/functions/tuple-map-functions#mapfromarrays), [`groupArray`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/grouparray)

Output is one row per `market_a` carrying a `Map(String, Float64)` of the other markets to their correlations — the client unfolds it into whatever shape it wants. Useful when the basket is parameterised at query time.

## Extending it

* **Different basket** — change the `IN (...)` list. Adding a market costs nothing in query length.
* **Rolling window** — replace `corr(a.ret, b.ret)` aggregate with a windowed `corr(...) OVER (... ROWS BETWEEN N PRECEDING AND CURRENT ROW)` to track how correlations change over time (e.g., regime detection).
* **Different return type** — `ln(close / lag) - 1` for log returns instead of simple returns. Pearson correlation of log returns vs simple returns is virtually identical for daily crypto, but the switch is one expression.


# Mean reversion z-score

A 20-day rolling **z-score** signal generator — the textbook mean-reversion idiom. When the price drifts more than `1.25 σ` away from its rolling mean, mark it as oversold (`signal = +1`) or overbought (`signal = -1`). Otherwise flat (`0`).

```
ma_20[t]   = mean of close[t-19 … t]
std_20[t]  = sample std of close[t-19 … t]   (ddof = 1)
zscore[t]  = (close[t] − ma_20[t]) / std_20[t]
signal[t]  = +1   if zscore < −1.25
             −1   if zscore > +1.25
              0   otherwise
```

## Query

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    20 AS window_size,
    1.25 AS n_std,
    candles AS (
        SELECT toDate(start) AS day, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market = 'BTC-USDT'
          AND start BETWEEN start_date AND end_date
    ),
    z AS (
        SELECT day, close,
               avg(close) OVER w AS ma_20,
               stddevSamp(close) OVER w AS std_20,
               (close - avg(close) OVER w) / nullIf(stddevSamp(close) OVER w, 0) AS zscore,
               count(*) OVER w AS valid_rows
        FROM candles
        WINDOW w AS (ORDER BY day ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
    )
SELECT day, close, ma_20, std_20, zscore,
       if(zscore < -n_std, 1, if(zscore > n_std, -1, 0)) AS signal
FROM z
WHERE valid_rows = window_size
ORDER BY day
```

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), [window functions (`avg`/`stddevSamp`/`count` OVER)](https://clickhouse.com/docs/sql-reference/window-functions), [`stddevSamp`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevsamp), [`nullIf`](https://clickhouse.com/docs/sql-reference/functions/conditional-functions#nullif), [`if`](https://clickhouse.com/docs/sql-reference/functions/conditional-functions#if)

Two SQL details worth flagging:

* **`stddevSamp`, not `stddevPop`.** Pandas `.rolling(N).std()` defaults to `ddof = 1` (Bessel's correction). Mixing pop / sample here changes the z-score by a factor of `√(N / (N − 1)) ≈ 1.026` for `N = 20` — small numerically but enough to flip a signal that's hovering near `±1.25`. Use `stddevSamp` to match pandas exactly.
* **`count(*) OVER w = window_size`** filters out the first 19 rows before the rolling window has filled. Same effect as `dropna(subset=['zscore'])` in pandas.

## Output (2024)

347 rows × 6 columns. The first few signal-firing days:

| day        | close    | ma\_20   | std\_20 | zscore | signal |
| ---------- | -------- | -------- | ------- | ------ | ------ |
| 2024-01-22 | 39568.02 | 43335.61 | 1974.41 | −1.91  | +1     |
| 2024-01-23 | 39897.60 | 43188.22 | 2117.76 | −1.55  | +1     |
| 2024-01-24 | 40084.88 | 42984.91 | 2213.47 | −1.31  | +1     |
| 2024-02-07 | 44349.60 | 42064.01 | 1310.55 | +1.74  | −1     |
| 2024-02-08 | 45288.65 | 42245.49 | 1490.48 | +2.04  | −1     |
| 2024-02-09 | 47132.77 | 42517.32 | 1839.83 | +2.51  | −1     |

Signal counts for the year: **53 long, 99 short, 195 flat**. Short signals dominate because BTC trended upward through 2024 — the rolling mean lagged the price most of the time, pushing the z-score above the upper threshold whenever the rally re-accelerated.

## Extending it

* **Different window or threshold** — change `window_size` and `n_std`. Tighter window (e.g. `10`) reacts faster but flips more often; wider window (`60`) is smoother but slower.
* **Backtest the strategy** — feed `signal` into a cumulative-returns CTE: `signal[t-1] · pct_change(close)[t]` then a running product via `exp(sum(log(1 + ...)))` (see [Maximum drawdown & Calmar](/sql-cookbook/max-drawdown-calmar) for the cumprod trick).


# DCA vs Lump-Sum

A side-by-side **monthly running comparison** of two contribution strategies on the same total budget:

* **DCA (Dollar-Cost Averaging)** — invest a fixed `$1000` at the start of each month, accumulating BTC at whatever the price is that month.
* **Lump-sum** — at month `T`, this represents "what if I had instead invested all `T × $1000` at month 0's price?". Useful as a baseline to read off DCA's smoothing-vs-opportunity-cost trade-off at a glance. 12-31`to match the parity-tested baseline; for live data swap the two date literals for a relative window like`now() - interval 5 year\`.

Three SQL details worth flagging:

## Query

```sql
WITH
    '2020-01-01' AS start_date,
    '2024-12-31' AS end_date,
    1000 AS monthly_contribution,
    monthly AS (
        SELECT toStartOfMonth(toDate(start)) AS month,
               argMin(toFloat64(close), start) AS open_price
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market = 'BTC-USDT'
          AND start BETWEEN start_date AND end_date
        GROUP BY month
    )
SELECT month,
       open_price,
       sum(monthly_contribution) OVER w AS cumulative_invested,
       sum(monthly_contribution / open_price) OVER w AS dca_btc,
       sum(monthly_contribution / open_price) OVER w * open_price AS dca_portfolio_value,
       sum(monthly_contribution) OVER w / first_value(open_price) OVER w AS lumpsum_btc,
       sum(monthly_contribution) OVER w / first_value(open_price) OVER w * open_price AS lumpsum_portfolio_value
FROM monthly
WINDOW w AS (ORDER BY month)
ORDER BY month
```

Functions used: [`toStartOfMonth`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofmonth), [`toDate`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#todate), [`argMin`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/argmin), [`toFloat64`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tofloat64), [window functions (`first_value`/`sum` OVER)](https://clickhouse.com/docs/sql-reference/window-functions#first_value)

Three SQL details worth flagging:

* **Monthly bucket via `toStartOfMonth(toDate(start))`** + `argMin(close, start)` picks the first daily close in each calendar month — same as `groupby('month').first()` in pandas. We **buy and value at the same monthly open price**, so the latest month's row honestly shows zero return on its just-purchased contribution; earlier rows reflect the appreciation since that month.
* **DCA accumulates** via `sum(monthly_contribution / open_price) OVER w` — one running sum, not a manual loop. Multiply by the current month's open to mark-to-market.
* **Lump-sum** is anchored to month 0's price via `first_value(open_price) OVER w`. At every row, `cumulative_invested / first_open_price` gives the BTC count you'd hold if you'd put the whole running total in at the start.

## Output (2020 — 2024, 60 monthly rows)

First and last few:

| month      | open\_price | cumulative\_invested | dca\_btc | dca\_portfolio\_value | lumpsum\_btc | lumpsum\_portfolio\_value |
| ---------- | ----------- | -------------------- | -------- | --------------------- | ------------ | ------------------------- |
| 2020-01-01 | 7200.85     | 1,000                | 0.14     | 1,000.00              | 0.14         | 1,000.00                  |
| 2020-02-01 | 9384.61     | 2,000                | 0.25     | 2,303.26              | 0.28         | 2,606.53                  |
| 2020-03-01 | 8531.88     | 3,000                | 0.36     | 3,093.98              | 0.42         | 3,554.53                  |
| ...        | ...         | ...                  | ...      | ...                   | ...          | ...                       |
| 2024-10-01 | 60805.78    | 58,000               | 2.57     | 156,432.34            | 8.05         | 489,766.52                |
| 2024-11-01 | 69496.01    | 59,000               | 2.59     | 179,789.31            | 8.19         | 569,413.97                |
| 2024-12-01 | 97185.18    | 60,000               | 2.60     | 252,422.44            | 8.33         | 809,780.90                |

After 60 months on `BTC-USDT`:

|              | Final BTC | Final value | Return on $60k |
| ------------ | --------- | ----------- | -------------- |
| **DCA**      | 2.60      | $ 252,422   | +320.7 %       |
| **Lump-sum** | 8.33      | $ 809,781   | +1,249.6 %     |

## Extending it

* **Different cadence** — change `toStartOfMonth(...)` to `toStartOfWeek(...)` for weekly DCA, or `toStartOfQuarter(...)` for quarterly. The contribution amount adjusts to match.
* **Different basket** — the SQL pattern is single-asset; for a multi-asset DCA wrap the inner CTE with a `PARTITION BY market` on every window function and add `market` to the final `ORDER BY`.
* **Variable contributions** — replace the constant `1000 AS monthly_contribution` with a join against a table or values list of `(month, contribution)` to model irregular schedules.


# Bollinger Bands

The canonical 20-period, 2σ **Bollinger Bands** in one CTE chain — plus an integer band-cross signal column for trade triggers. Same window-function pattern as [Mean reversion z-score](/sql-cookbook/mean-reversion-zscore), just emitted as the conventional `(middle, upper, lower)` triple instead of a z-score.

```
middle_band[t] = mean of close[t-19 … t]
std_20[t]      = population std of close[t-19 … t]   (ddof = 0)
upper_band[t]  = middle_band[t] + 2 · std_20[t]
lower_band[t]  = middle_band[t] − 2 · std_20[t]
signal[t]      = +1 if close < lower_band   (oversold → potential long)
                 −1 if close > upper_band   (overbought → potential short)
                  0 otherwise
```

## Query

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    20 AS window_size,
    2.0 AS n_std,
    candles AS (
        SELECT toDate(start) AS day, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market = 'BTC-USDT'
          AND start BETWEEN start_date AND end_date
    ),
    bands AS (
        SELECT day, close,
               avg(close) OVER w AS middle_band,
               avg(close) OVER w + n_std * stddevPop(close) OVER w AS upper_band,
               avg(close) OVER w - n_std * stddevPop(close) OVER w AS lower_band,
               count(*) OVER w AS valid_rows
        FROM candles
        WINDOW w AS (ORDER BY day ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
    )
SELECT day, close, middle_band, upper_band, lower_band,
       if(close < lower_band, 1, if(close > upper_band, -1, 0)) AS signal
FROM bands
WHERE valid_rows = window_size
ORDER BY day
```

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), [window functions (`avg`/`stddevPop`/`count` OVER)](https://clickhouse.com/docs/sql-reference/window-functions), [`stddevPop`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevpop), [`if`](https://clickhouse.com/docs/sql-reference/functions/conditional-functions#if)

Two SQL details worth flagging:

* **`stddevPop`, not `stddevSamp`.** Canonical Bollinger Bands use population std (`ddof = 0`); TA-Lib's `BBANDS()` follows that. Pandas' `.rolling(N).std()` defaults to sample std, so the reproducible Python uses `.rolling(20).std(ddof=0)` explicitly. Mixing pop / sample changes the band width by `√(N / (N − 1)) ≈ 1.026` for `N = 20`.
* **`count(*) OVER w = window_size`** filters rows where the rolling window hasn't yet filled — same role as `dropna()` after pandas `.rolling(20)`.

## Output (2024)

347 rows × 6 columns. **16 long signals, 28 short signals, 303 flat** across the year — short-heavy because BTC repeatedly broke through the upper band on the way up. Sample rows showing all three signal values:

| day        | close     | middle\_band | upper\_band | lower\_band | signal |
| ---------- | --------- | ------------ | ----------- | ----------- | ------ |
| 2024-02-08 | 45288.65  | 42245.49     | 45150.97    | 39340.01    | −1     |
| 2024-02-13 | 49699.59  | 44244.18     | 49712.15    | 38776.21    | 0      |
| 2024-04-12 | 67116.52  | 69110.15     | 72347.79    | 65872.51    | 0      |
| 2024-04-13 | 63924.51  | 68945.88     | 72822.79    | 65068.97    | +1     |
| 2024-04-15 | 63419.99  | 68406.57     | 73069.12    | 63744.02    | +1     |
| 2024-08-05 | 54018.81  | 64803.52     | 71971.54    | 57635.50    | +1     |
| 2024-12-16 | 106058.66 | 98941.58     | 104757.22   | 93125.94    | −1     |

## Extending it

* **Different window or σ width** — change `window_size` and `n_std`. Common variants: `(10, 1.5)` for tighter bands, `(50, 2.5)` for wider/slower.
* **Z-score view** — same numbers, different presentation: `(close - middle_band) / stddevPop(close) OVER w` is the [z-score](/sql-cookbook/mean-reversion-zscore) and it crosses ±`n_std` at exactly the same rows where `close` crosses the bands.
* **Other indicators in the same CTE chain** — RSI, MACD, Donchian channels all decompose to one or two window functions each. Extend `bands` with extra columns rather than another CTE.


# Cross-exchange arbitrage

Per-minute **cross-exchange spread matrix** for one market across many venues — the canonical "find the price gap" demo.

## Query

```sql
WITH
    '2024-12-31' AS day,
    p AS (
        SELECT start, exchange, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1)
        WHERE market = 'BTC-USDT'
          AND exchange IN ('binance', 'okx', 'kucoin', 'gateio')
          AND start >= toDateTime(day)
          AND start <  toDateTime(day) + INTERVAL 1 DAY
    )
SELECT a.start,
       a.exchange AS buy_ex,
       b.exchange AS sell_ex,
       a.close AS buy_price,
       b.close AS sell_price,
       (b.close - a.close) / a.close * 100 AS spread_pct
FROM p a
JOIN p b ON a.start = b.start
WHERE a.exchange < b.exchange
ORDER BY a.start, buy_ex, sell_ex
```

Functions used: [`toFloat64`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tofloat64), [`toDateTime`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#todatetime)

Two SQL details worth flagging:

* **`a.exchange < b.exchange`** is the standard ordered-pair trick to avoid double-counting `(binance, okx)` and `(okx, binance)`. With 4 exchanges you get C(4, 2) = 6 ordered pairs, not 16 of the unordered-and-self-joined Cartesian product.
* **No threaded fetcher.** `api.ohlcv(...)` returns simultaneous cross-exchange snapshots by virtue of the WHERE clause — every row for `start = T` is for the same `T`. The repo's threading exists only because each REST call is independent and synchronous.

## Output (2024-12-31)

8,640 rows = 1,440 minutes × 6 ordered pairs. Top 8 absolute spreads of the day:

| start               | buy\_ex | sell\_ex | buy\_price | sell\_price | spread\_pct |
| ------------------- | ------- | -------- | ---------- | ----------- | ----------- |
| 2024-12-31 16:54:00 | binance | kucoin   | 94903.65   | 95000.00    | +0.102      |
| 2024-12-31 16:54:00 | gateio  | kucoin   | 94904.20   | 95000.00    | +0.101      |
| 2024-12-31 13:40:00 | kucoin  | okx      | 95561.60   | 95656.00    | +0.099      |
| 2024-12-31 16:54:00 | kucoin  | okx      | 95000.00   | 94906.60    | −0.098      |
| 2024-12-31 13:36:00 | kucoin  | okx      | 95600.00   | 95693.60    | +0.098      |
| 2024-12-31 13:35:00 | kucoin  | okx      | 95600.00   | 95691.80    | +0.096      |
| 2024-12-31 13:36:00 | binance | kucoin   | 95688.98   | 95600.00    | −0.093      |
| 2024-12-31 13:50:00 | binance | kucoin   | 95891.73   | 95804.20    | −0.091      |

A few clusters of \~0.1 % gaps — well above retail trading-fee breakeven (\~0.04 % each side), but small enough that the venues re-arbed within a minute or two. The mean / std distribution per pair tells the same story:

| pair             | mean   | std   | min    | max    |
| ---------------- | ------ | ----- | ------ | ------ |
| binance ↔ gateio | −0.010 | 0.010 | −0.043 | +0.047 |
| binance ↔ kucoin | −0.003 | 0.012 | −0.093 | +0.102 |
| binance ↔ okx    | 0.000  | 0.008 | −0.025 | +0.033 |
| gateio ↔ kucoin  | +0.007 | 0.013 | −0.085 | +0.101 |
| gateio ↔ okx     | +0.010 | 0.011 | −0.036 | +0.057 |
| kucoin ↔ okx     | +0.003 | 0.012 | −0.098 | +0.099 |

`kucoin` is the noisiest leg — its standard deviation against every other venue is the largest, and it's the buy/sell side of every top-8 spread row.

## Extending it

* **More exchanges** — append to the `IN (...)` list. Each addition gives you `(N − 1)` new pair rows per minute, no extra wrappers.
* **Filter to actionable spreads** — append `HAVING spread_pct > 0.04` (or whatever your round-trip fee is). The alert-dispatcher in the article's repo collapses to a single `WHERE`.
* **Different market** — the `WHERE market = 'BTC-USDT'` is the only market constraint. Add `OR market IN (...)` and a `GROUP BY market` to scan a basket simultaneously.
* **Higher-resolution timing** — 1-minute candles are the smallest exposed via `api.ohlcv(...)`. For sub-minute work, JOIN against `api.trade` directly with a tolerance window (`WHERE abs(a.timestamp - b.timestamp) < INTERVAL 100 MILLISECOND`).


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


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


# OHLCV streaming download

Most pages in this section show a SQL query that *replaces* a chunk of pandas. This one is the opposite: a small Python wrapper around a trivially simple SQL query, designed to **download large volumes of data** straight to a Parquet file.

The pandas idiom this replaces is `client.query_df(sql)` followed by `df.to_parquet(path)` — fine for thousands of rows, increasingly painful past a few hundred thousand, an outright OOM at multi-million scale.

## Query

The SQL itself is a one-liner — a multi-exchange BTC-USDT 1-minute OHLCV pull, no aggregation, no analytics:

```sql
SELECT start, exchange, market,
       toFloat64(open)   AS open,
       toFloat64(high)   AS high,
       toFloat64(low)    AS low,
       toFloat64(close)  AS close,
       toFloat64(volume) AS volume
FROM api.ohlcv(candle_duration_in_minutes = 1)
WHERE market = 'BTC-USDT'
  AND exchange IN ('binance', 'okx', 'kucoin', 'gateio')
  AND start >= toDateTime('2024-01-01')
  AND start <  toDateTime('2024-12-31') + INTERVAL 1 DAY
ORDER BY exchange, start
```

Functions used: [`toFloat64`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tofloat64), [`toDateTime`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#todatetime)

For the year of 2024 across the four exchanges, that's about 525,600 minutes × 4 exchanges ≈ **2.1 million rows**. `client.query_df(sql)` would peak around 500 MB – 1 GB of resident memory before writing anything.

## Python streaming wrapper

`clickhouse-connect` exposes `Client.raw_stream(query, fmt='...')` — the server formats the result row-by-row in a streaming pipeline and ships it via HTTP chunked transfer. Wrap it with a byte-loop into a file and you get bounded RAM regardless of result size:

```python
from pathlib import Path

from clickhouse_connect.driver import Client

def download_to_parquet(client: Client, query: str, out_path: Path) -> int:
    """Stream `query` directly to `out_path` as Parquet, return bytes written."""
    out_path.parent.mkdir(parents=True, exist_ok=True)
    bytes_written = 0
    with (
        client.raw_stream(query, fmt="Parquet") as stream,
        out_path.open("wb") as f,
    ):
        for chunk in stream:
            f.write(chunk)
            bytes_written += len(chunk)
    return bytes_written

client = clickhouse_connect.get_client(
    host='<provided_database_url>', port=8443, secure=True,
    username='<username>', password='<password>',
    database='api')

sql = """
SELECT *
FROM api.ohlcv(candle_duration_in_minutes = 1)
WHERE market = 'BTC-USDT'
  AND exchange IN ('binance', 'okx', 'kucoin', 'gateio')
  AND start >= toDateTime('2024-01-01')
  AND start <  toDateTime('2024-12-31') + INTERVAL 1 DAY
ORDER BY exchange, start
"""

download_to_parquet(client, sql, Path('btc_ohlcv_2024.parquet'))
```

Three things to notice:

* **`fmt='Parquet'`** picks the [ClickHouse format](https://clickhouse.com/docs/en/interfaces/formats#data-format-parquet) — typed columns, columnar layout, very compact on disk. Other useful alternatives: `CSVWithNames`, `JSONEachRow`, `Arrow`, `TSVWithNames`.
* **No pandas, no NumPy, no per-row Python.** The Parquet bytes leave ClickHouse and land in the file with one Python-level allocation per HTTP chunk (\~64 KB by default).
* **Order of magnitude on the koinju cluster.** The December-2024 slice of the query above (4 exchanges × 31 days × 1-minute candles ≈ 178 K rows) downloads as **5.4 MB Parquet in \~5.3 seconds**. Extrapolated linearly the full 2024 pull is \~65 MB / \~65 seconds — bounded by ClickHouse's Parquet writer and your network throughput, not by client RAM. (For comparison, the same data as `CSVWithNames` is \~17 MB / \~7 seconds — Parquet is \~3× smaller and \~30 % faster end-to-end.)

## Reading the Parquet back

```python
import pyarrow.parquet as pq

table = pq.read_table('btc_ohlcv_2024.parquet')
# table.num_rows, table.column_names, table.to_pandas(), …
```

A note on schema: ClickHouse's Parquet writer maps `DateTime` columns to Parquet `uint32` (Unix-epoch seconds) and `LowCardinality(String)` to Parquet `binary`. Both are conventional and most readers (pyarrow, DuckDB, Spark) decode them transparently when converted to typed results — but if you're parsing the Parquet at the byte level, expect those physical types rather than `timestamp[us]` / `utf8`.

## Companion code

This page's repo companion at [`api/python_vs_sql/ohlcv_streaming/`](https://gitlab.com/koinju/connector/exporter/-/tree/master/api/python_vs_sql/ohlcv_streaming) packages the wrapper as a runnable `download.py`, plus a smoke test that exercises the pipeline on a tiny window (1 hour × 1 exchange) — verifying that the script actually writes a non-empty Parquet file with the right schema and row count, without burning quota on a multi-million-row pull every CI run.

## Extending it

* **Different query** — the wrapper is data-agnostic. Pass any SQL string (a multi-asset trade pull, a candle fan-out, a custom aggregate over a long window) and it streams the result. The only cost of a complex query is server-side compute time, not client memory.
* **Different format** — change `fmt='Parquet'` to `'CSVWithNames'`, `'JSONEachRow'`, `'Arrow'`, etc. CSV is friendlier to text-only tooling; Arrow IPC is the fastest in-process round-trip to pyarrow.
* **Resumable downloads** — the simple version doesn't checkpoint. For genuinely huge pulls, partition the SQL by month or by exchange and run one wrapper call per partition; failures retry one partition, not the whole pull.
* **Direct upload to S3 / object storage** — replace `out_path.open('wb')` with a streaming upload buffer (`boto3.upload_fileobj` and friends accept iterators). The bytes never touch local disk.


# Multi-exchange vol surface

Pulling a full BTC option chain across **Deribit, Binance, OKX, and Bybit** is a single SQL query — and once the data is in a DataFrame, four side-by-side vol surfaces, an ATM term-structure curve, and a single-expiry smile are all a short Plotly script away.

This article walks through the query and each plot. The full notebook is downloadable at the bottom.

## The query

```sql
WITH (
  SELECT max(timestamp)
  FROM api.option_chains
  WHERE underlying_asset = 'BTC' AND timestamp > now() - INTERVAL 30 MINUTE
) AS latest_ts
SELECT
  exchange,
  toDate(expiration)                                AS expiry,
  toFloat64(strike)                                 AS strike,
  toUInt32(date_diff('day', timestamp, expiration)) AS dte,
  toFloat64(mark_iv)                                AS iv,
  toFloat64(underlying_price)                       AS spot,
  toFloat64(abs(strike - underlying_price))         AS moneyness
FROM api.option_chains
WHERE underlying_asset = 'BTC'
  AND mark_iv != 0
  AND timestamp >= latest_ts - INTERVAL 5 MINUTE  -- snapshots refresh every 5 minutes
  AND dte BETWEEN 2 AND 150
ORDER BY exchange, expiry, strike
```

Three things worth noting:

* **No `WHERE exchange = …`** — the table holds every venue's snapshot at the same `timestamp` cadence, so omitting the exchange filter returns all four in one round-trip.
* **`timestamp >= latest_ts - 5 min`** — different exchanges' materialized views flush a few seconds apart. Widening the latest-snapshot anchor by one snapshot tick (5 minutes) catches all four venues' freshest data without doubling rows.
* **`dte BETWEEN 2 AND 150`** — server-side crop of same-day and far-dated expiries. Write it as `BETWEEN`: a chained `2 <= dte <= 150` parses in ClickHouse as `(2 <= dte) <= 150`, which is always true and silently disables the filter.

`underlying_price` (aliased `spot`) and `moneyness = abs(strike − underlying_price)` are computed server-side, so locating the at-the-money strike per expiry is a pure sort rather than a pandas step. The `toFloat64(...)` casts turn the `Decimal(38, 18)` columns into plot-ready floats in the query — no pandas `.astype(float)` needed. The IV column is already in consistent annualized-percent units across all four exchanges (see the [Option chain](/data/option-chain) page for the normalization detail).

## Interpolation onto a shared grid

Listed strikes differ across venues. To compare surfaces directly, interpolate each onto the same `(dte, strike)` grid. We also crop the strike axis to `[40k, 160k]` to drop deep-wing noise. It stays a client-side constant so you can re-tune the plotted band without re-issuing the query — but `spot` is already in the result set, so you could equally express this as a server-side `moneyness` filter.

```python
GRID_N = 80
STRIKE_MIN, STRIKE_MAX = 40_000, 160_000

df_f = df[df['strike'].between(STRIKE_MIN, STRIKE_MAX)]
x_grid = np.linspace(df_f['dte'].min(), df_f['dte'].max(), GRID_N)
y_grid = np.linspace(df_f['strike'].min(), df_f['strike'].max(), GRID_N)
xx, yy = np.meshgrid(x_grid, y_grid)

def surface_for(exchange):
    sub = df_f[df_f['exchange'] == exchange]
    pts = sub[['dte', 'strike']].to_numpy()
    vals = sub['iv'].to_numpy()
    z = griddata(pts, vals, (xx, yy), method='linear')
    z_fill = griddata(pts, vals, (xx, yy), method='nearest')
    return np.where(np.isnan(z), z_fill, z)
```

`griddata` with `method='linear'` produces NaNs outside the convex hull of each exchange's listed instruments; a second `'nearest'` pass fills those edges so the surface is plottable everywhere on the shared grid.

## The surfaces

One Plotly `Surface` trace per exchange, laid out in a 2-column grid. The exchange list comes from the dataframe (so a query that returns only 3 venues for some asset still plots correctly), with a shared colour scale across all panels:

```python
exchanges = sorted(df_f['exchange'].unique())
n = len(exchanges)
cols = 2 if n > 1 else 1
rows = (n + cols - 1) // cols

fig = make_subplots(
    rows=rows, cols=cols,
    specs=[[{'type': 'surface'}] * cols for _ in range(rows)],
    subplot_titles=[e.title() for e in exchanges],
)
iv_min, iv_max = df_f['iv'].min(), df_f['iv'].max()
for i, ex in enumerate(exchanges):
    fig.add_trace(
        go.Surface(x=xx, y=yy, z=surface_for(ex), colorscale='Viridis',
                   cmin=iv_min, cmax=iv_max, showscale=(i == 0)),
        row=(i // cols) + 1, col=(i % cols) + 1,
    )
fig.show()
```

<figure><img src="/files/16H9JtjJR67hzit82Sed" alt="Four BTC implied-volatility surfaces — Binance, Bybit, Deribit, OKX"><figcaption><p>BTC implied-volatility surface on each venue — shared colour scale, latest 5-minute snapshot. The wing lift along the strike axis is the smile; the gentle rise along DTE is the term structure.</p></figcaption></figure>

## ATM term structure

At-the-money IV as a function of time to expiry. `moneyness` comes from the query, so keeping the strike closest to spot for each `(exchange, expiry)` is just a sort + dedup — one line per venue:

```python
atm = (df.sort_values('moneyness')
         .drop_duplicates(['exchange', 'dte'])
         .sort_values(['exchange', 'dte']))

fig = go.Figure()
for ex in sorted(atm['exchange'].unique()):
    s = atm[atm['exchange'] == ex]
    fig.add_trace(go.Scatter(x=s['dte'], y=s['iv'],
                             mode='lines+markers', name=ex.title()))
fig.show()
```

<figure><img src="/files/8VCGDWt4Qu9OwOjAhJw3" alt="BTC ATM implied-volatility term structure across four venues"><figcaption><p>ATM mark IV vs DTE. An upward slope is contango (longer-dated vol richer); a downward slope is backwardation — the classic near-term event/stress signature.</p></figcaption></figure>

The four venues track each other tightly at ATM — this is the least-arbitrageable slice. Where they fan out is usually a venue carrying an expiry the others don't list.

## Volatility smile

Fix the expiry, sweep the strike. Per venue, take the listed expiry closest to 30 DTE (they line up on the same date here), crop to the same strike band as the surface, and overlay all four:

```python
TARGET_DTE = 30
chosen = (df.assign(gap=(df['dte'] - TARGET_DTE).abs())
            .sort_values('gap')
            .drop_duplicates('exchange')[['exchange', 'dte']])
smile = (df.merge(chosen, on=['exchange', 'dte'])
           .query('strike >= @STRIKE_MIN and strike <= @STRIKE_MAX')
           .sort_values('strike'))

fig = go.Figure()
for ex in sorted(smile['exchange'].unique()):
    s = smile[smile['exchange'] == ex]
    fig.add_trace(go.Scatter(x=s['strike'], y=s['iv'], mode='lines+markers',
                             name=f"{ex.title()} ({int(s['dte'].iloc[0])}d)"))
fig.add_vline(x=df['spot'].median(), line_dash='dash', line_color='gray',
              annotation_text='~spot')
fig.show()
```

<figure><img src="/files/SsEuj5ODXpubVGDehkfv" alt="BTC volatility smile at ~30 DTE across four venues"><figcaption><p>Mark IV vs strike at the ~30 DTE expiry. Wing steepness flags tail pricing; a vertical gap between venues at the same strike is cross-venue dispersion.</p></figcaption></figure>

The smile bottoms out near spot and lifts on both wings. The call wing is where it gets interesting: a gap between venues at the same strike and expiry is a candidate arb — after netting funding, basis, and leg-out cost.

## Download

{% file src="/files/KTYKPk6k17st0OCM9FZT" %}
Jupyter notebook — the full pipeline from connection to all three plots.
{% endfile %}

Connection setup (host, credentials) at the top of the notebook follows the same template as [How to connect](/how-to-connect). Replace the placeholders with your provisioned values.