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)

⚠️ 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

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

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


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

get

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.

Authorizations
Query parameters
exchangestringRequired

The name of the exchange to filter by

marketstringRequired

The universal market symbol to filter by

start_datetimestring · date-timeRequired

The start time for the trade data in ISO 8601 format (e.g., '2023-01-01T00:00:00Z') or any format that can be parsed by the parseDateTime64BestEffort

end_datetimestring · date-timeRequired

The end time for the trade data in ISO 8601 format (e.g., '2023-01-02T00:00:00Z') or any format that can be parsed by the parseDateTime64BestEffort

Responses
200

OK

application/json
get
/trade
GET /trade?exchange=text&market=text&start_datetime=2025-10-30T18%3A27%3A01.137Z&end_datetime=2025-10-30T18%3A27%3A01.137Z HTTP/1.1
Host: api.koinju.io
Authorization: Basic username:password
Accept: */*
200

OK

[
  [
    {
      "exchange": "bitfinex",
      "market": "BTC-USD",
      "side": "buy",
      "quantity": 0.2721858,
      "price": 14.5373664,
      "timestamp": "2013-01-14T16:47:23Z",
      "trade_id": "4145"
    },
    {
      "exchange": "bitfinex",
      "market": "BTC-USD",
      "side": "buy",
      "quantity": 103,
      "price": 14.5373664,
      "timestamp": "2013-01-14T16:47:24Z",
      "trade_id": "4146"
    }
  ]
]
PreviousMarket listNextOHLCV

Last updated

Was this helpful?