# OHLCV

## SQL API

### `api.ohlcv`

**Aggregated OHLCV Candles with Dynamic Resolution**\
This parameterized view provides on-the-fly aggregation of raw 1-minute candles into customizable time intervals (e.g., 5min, 1hr, 4hr).

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

#### 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         | Normalized 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     |
| ------------------ | --------------- | -------------- | ------------ | ------------ |
| Daily/Hourly OHLCV | Full history    | Full history   | Full history | Full history |
| 1-min OHLCV        | Rolling 1 month | Rolling 1 year | Full history | Full history |
| Futures OHLCV      | —               | —              | Full history | Full history |
| Options OHLCV      | —               | —              | —            | Full history |

{% hint style="info" %}
Queries requesting data outside your tier's allowed time window will return only the data within the window — no error is raised. See [Pricing](/pricing.md) for details.
{% endhint %}

***

#### ⚠️ Critical Performance Warning

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!

***

#### Advanced Example Queries

#### Exchange Volume Leaderboard

Total BTC-USD volume this month per exchange

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

*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 = 'BTC-USD'
    AND exchange = 'kraken'
    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
```

*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);
```

*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 in ISO 8601 format (e.g., '2023-01-01T00:00:00Z')\nor any format that can be parsed by the `parseDateTime64BestEffort`\n","schema":{"type":"string","format":"date-time"}},{"name":"end_datetime","in":"query","required":true,"description":"The end time for the OHLCV data in ISO 8601 format (e.g., '2023-01-02T00:00:00Z')\nor any format that can be parsed by the `parseDateTime64BestEffort`\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"}}}}}}}}}}}}
```


---

# Agent Instructions: Querying This Documentation

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

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

```
GET https://docs.koinju.io/data/ohlcv.md?ask=<question>
```

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

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