> For the complete documentation index, see [llms.txt](https://docs.koinju.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.koinju.io/data/public-trades.md).
# 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.md) 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.
```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"}}}}}}}}}}}
```
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## 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, and the optional `goal` query parameter:
```
GET https://docs.koinju.io/data/public-trades.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
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.