# Public trades #### `api.trade` View Documentation This view provides access to raw trade ticks across all supported exchanges, normalized into a consistent schema with unified market symbols. | Column | Type | Description | | ----------- | -------------- | ------------------------------------------- | | `exchange` | String) | Exchange name (e.g., "binance\_futures") | | `market` | String | Normalized 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 | — | — | | Developer | Rolling 90 days | — | — | | Professional | Rolling 1 year | Rolling 1 year | — | | Business | Full history | Full history | 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](https://docs.koinju.io/pricing) for details. {% endhint %} *** #### ⚠️ Extreme Performance Warning 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 ``` *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 ``` *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 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 trade 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":{"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"}}}}}}}}}}}} ```