# Historical Option Trades API (SQL)

> POST /api/historical/sql reference: 15-minute-delayed ClickHouse SELECT over RawOptionTrades, schema fields, restrictions, examples, and trial limits.

The Option Trades API provides secure access to historical option trading data through SQL queries. Every visible trade is at least 15 minutes old, measured from its execution timestamp.

## Endpoint

**POST** `https://www.optiondata.io/api/historical/sql`

Execute secure SELECT queries against option trades data.

### Headers

| Name | Value |
|------|-------|
| Authorization | `Bearer YOUR_API_KEY` (recommended) |
| Content-Type | `application/json` or `application/x-www-form-urlencoded` |

The bearer header takes precedence over body `api_key`. A malformed or unsupported `Authorization` header is rejected rather than falling back to the body key. JSON and form-encoded request bodies are supported.

### Request Body

| Name | Type | Required | Description |
|------|------|----------|-------------|
| api_key | string | No | Legacy body authentication fallback when `Authorization` is absent. Prefer the bearer header. |
| sql | string | Yes | The SQL query to execute. |

## Data Tables

### RawOptionTrades

This table contains all the historical trades.

**Notes:**
- Table names are case-insensitive.
- Invalid table names will result in an error.
- Only whitelisted tables are accessible.

## SQL Restrictions

### Allowed Operations
- SELECT statements only
- Standard SQL functions (COUNT, SUM, AVG, etc.)
- WHERE clauses with filtering
- ORDER BY and LIMIT clauses
- GROUP BY clauses

### Forbidden Operations
- INSERT, UPDATE, DELETE statements
- DROP, CREATE, ALTER statements
- UNION operations
- Stored procedures (EXEC, CALL)
- Comments in SQL (rejected, not stripped)

## Schema Definition

### Fields

| Name | Type | Description |
|------|------|-------------|
| date | Date | The date the trade was executed, format: YYYY-MM-DD. This column is the partition key. |
| time | DateTime64(3, 'America/New_York') | The timestamp of the trade with millisecond precision in America/New_York timezone. Format: YYYY-MM-DD HH:MM:SS.mmm. Part of primary key. |
| symbol | LowCardinality(String) | Ticker symbol (TSLA, AAPL, SPY, etc.). Part of primary key - filter by symbol for best performance. |
| put_call | Enum8('CALL' = 1, 'PUT' = 2) | Option type: 'CALL' or 'PUT'. |
| strike | Decimal(9,3) | Strike price of the option contract. Indexed column. |
| expiration_date | Date | The date on which the option expires, format: YYYY-MM-DD. Indexed column. |
| size | UInt32 | Number of contracts traded in this transaction. |
| price | Decimal(9,4) | Trade price per contract. |
| bid | Decimal(9,4) | Best bid price at time of trade. |
| ask | Decimal(9,4) | Best ask price at time of trade. |
| underlying_price | Decimal(9,4) | Price of the underlying stock at time of trade. |
| iv | Decimal(9,4) | Implied volatility (decimal, e.g. 0.35 = 35%). |
| delta | Decimal(9,4) | Option delta (-1 to 1). |
| gamma | Decimal(9,6) | Option gamma. |
| oi | UInt32 | Open Interest - total number of outstanding option contracts. |
| dei | Decimal(9,4) | Delta exposure impact relative to daily stock volume. |

### Derived Fields

These fields are not stored in `RawOptionTrades`. You can derive them in your application logic after receiving the raw data.

| Column | Status | Reason | Formula |
|--------|--------|--------|---------|
| id | Derivable | Frontend generates component keys | `sipHash64(concat(time, symbol, strike, price, size))` |
| trade_count | Derivable | RawOptionTrades stores individual trades only | `1` |
| expiry_days | Derivable | Simple date arithmetic | `dateDiff('day', date, expiration_date)` |
| premium | Derivable | Simple multiplication | `toFloat64(price) * size * 100` |
| option_symbol | Derivable | OCC format from components | `concat(symbol, YYMMDD, P/C, strike*1000)` |
| moneyness | Derivable | Compare strike vs underlying | `IF strike ~= underlying_price THEN 'ATM' ELSE IF ITM/OTM by put_call` |
| sentiment | Derivable | Inferred from side + put_call | `IF CALL+BUY='BULLISH', PUT+BUY='BEARISH', etc.` |
| side | Derivable | Compare price to bid/ask | `IF price > ask THEN 'AASK' ELSE IF price >= ask THEN 'ASK' ...` |
| daily_volume | Derivable | Aggregate from trades | `SUM(size) OVER (PARTITION BY date, symbol, strike, put_call, expiration_date)` |
| dex | Derivable | Delta exposure - equivalent shares the dealer must hedge | `delta * size * 100` |

## Examples

### Request Example

```
api_key=YOUR_API_KEY&sql=SELECT date, time, symbol, put_call, strike, expiration_date, size, price, bid, ask FROM RawOptionTrades WHERE date = (SELECT max(date) FROM RawOptionTrades) AND symbol = 'AAPL' ORDER BY time DESC LIMIT 20
```

### Code Examples

#### cURL

```bash
curl -X POST https://www.optiondata.io/api/historical/sql \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -H "Authorization: Bearer YOUR_API_KEY" \
  --data-urlencode "sql=SELECT date, time, symbol, put_call, strike, expiration_date, size, price, bid, ask FROM RawOptionTrades WHERE date = (SELECT max(date) FROM RawOptionTrades) AND symbol = 'AAPL' ORDER BY time DESC LIMIT 20"
```

#### Python

```python
import http.client

conn = http.client.HTTPSConnection("www.optiondata.io")
payload = "api_key=YOUR_API_KEY&sql=SELECT%20date%2C%20time%2C%20symbol%2C%20put_call%2C%20strike%2C%20expiration_date%2C%20size%2C%20price%2C%20bid%2C%20ask%20FROM%20RawOptionTrades%20WHERE%20date%20%3D%20(SELECT%20max(date)%20FROM%20RawOptionTrades)%20AND%20symbol%20%3D%20'AAPL'%20ORDER%20BY%20time%20DESC%20LIMIT%2020"
headers = {
  'Content-Type': 'application/x-www-form-urlencoded'
}
conn.request("POST", "/api/historical/sql", payload, headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))
```

#### JavaScript

```javascript
const axios = require('axios');
const qs = require('qs');
let data = qs.stringify({
  'api_key': 'YOUR_API_KEY',
  'sql': "SELECT date, time, symbol, put_call, strike, expiration_date, size, price, bid, ask FROM RawOptionTrades WHERE date = (SELECT max(date) FROM RawOptionTrades) AND symbol = 'AAPL' ORDER BY time DESC LIMIT 20"
});

let config = {
  method: 'post',
  maxBodyLength: Infinity,
  url: 'https://www.optiondata.io/api/historical/sql',
  headers: {
    'Content-Type': 'application/x-www-form-urlencoded'
  },
  data : data
};

axios.request(config)
.then((response) => {
  console.log(JSON.stringify(response.data));
})
.catch((error) => {
  console.log(error);
});
```

### Sample SQL Queries

#### Latest Trading Date
```sql
SELECT max(date) AS latest_date
FROM RawOptionTrades
```

#### Latest Symbol Trades
```sql
SELECT date, time, symbol, put_call, strike, expiration_date, size, price, bid, ask
FROM RawOptionTrades
WHERE date = (SELECT max(date) FROM RawOptionTrades)
AND symbol = 'AAPL'
ORDER BY time DESC
LIMIT 20
```

#### Symbol Flow Aggregate
```sql
SELECT
  symbol,
  put_call,
  COUNT(*) as total_trades,
  SUM(size) as total_contracts,
  ROUND(SUM(toFloat64(price) * size * 100), 2) as total_premium
FROM RawOptionTrades
WHERE date = (SELECT max(date) FROM RawOptionTrades)
AND symbol IN ('AAPL', 'TSLA', 'SPY')
GROUP BY symbol, put_call
ORDER BY total_premium DESC
LIMIT 12
```

#### Large Premium Trades
```sql
SELECT date, time, symbol, put_call, strike, expiration_date, size, price,
  ROUND(toFloat64(price) * size * 100, 2) as premium
FROM RawOptionTrades
WHERE date = (SELECT max(date) FROM RawOptionTrades)
AND symbol IN ('AAPL', 'TSLA', 'SPY')
AND size >= 50
ORDER BY premium DESC
LIMIT 20
```

#### Date Range Rollup
```sql
SELECT
  date,
  symbol,
  put_call,
  COUNT(*) as total_trades,
  SUM(size) as total_contracts,
  ROUND(SUM(toFloat64(price) * size * 100), 2) as total_premium
FROM RawOptionTrades
WHERE date BETWEEN '2025-03-03' AND '2025-03-07'
AND symbol = 'AAPL'
GROUP BY date, symbol, put_call
ORDER BY date DESC, total_premium DESC
LIMIT 50
```

## Review Response

### Success (200 OK)
```json
{
    "status": "SUCCESS",
    "data": [
        {
            "date": "2025-01-17",
            "time": "2025-01-17 09:30:15.123",
            "symbol": "TSLA",
            "put_call": "CALL",
            "strike": 420.000,
            "expiration_date": "2025-01-24",
            "size": 10,
            "price": 4.2500,
            "bid": 4.2000,
            "ask": 4.3000,
            "underlying_price": 418.5200,
            "iv": 0.4523,
            "delta": 0.5234,
            "gamma": 0.012345,
            "oi": 15234,
            "dei": 0.0012
        }
    ],
    "meta": {
        "entitlement": "active",
        "row_limit": 10000,
        "capped": false,
        "minimum_data_delay_minutes": 15
    }
}
```

### Errors

**Invalid API Key**
```json
{
  "status": "ERROR",
  "errorMsg": "API Key is required"
}
```

**Invalid SQL**
```json
{
  "status": "ERROR",
  "errorMsg": "Invalid SQL query: Only SELECT queries are allowed"
}
```

**Invalid query fields or expressions (422 Unprocessable Content)**
```json
{
  "status": "ERROR",
  "errorCode": "INVALID_QUERY",
  "reason": "UNKNOWN_IDENTIFIER",
  "errorMsg": "The query references a column, alias, or table identifier that is not available."
}
```

Use `reason` to correct the query without relying on database-internal error text. Possible values are `UNKNOWN_IDENTIFIER`, `UNKNOWN_FUNCTION`, `TYPE_MISMATCH`, `NUMERIC_OVERFLOW`, `SYNTAX_ERROR`, `INVALID_AGGREGATION`, `INVALID_ARGUMENTS`, and `INVALID_EXPRESSION`.

`NUMERIC_OVERFLOW` means an arithmetic expression exceeded its supported numeric precision or range. Cast fixed-precision operands before multiplying or aggregating them, for example: `SUM(toFloat64(price) * size * 100)`.

`QUERY_TOO_BROAD` also uses HTTP 422 when a valid query exceeds server read limits. `QUERY_TIMEOUT` uses HTTP 504. Unexpected service or infrastructure failures use HTTP 500 with `INTERNAL_ERROR`.

Unexpected HTTP 500 responses include an `X-Request-Id` response header. Include that reference when contacting support; it identifies the request without exposing the submitted SQL.

## FAQ

**Q: What database technology do you use?**  
A: ClickHouse over HTTPS. Submit SELECT-only SQL to `POST /api/historical/sql` against whitelisted tables (`RawOptionTrades` and related MVs).

**Q: What are the rate and row limits?**  
A: The **current default** is about **60 requests per 60 seconds** per customer on this endpoint, for trial and paid users, enforced on a best-effort basis. HTTP **429** includes `Retry-After`.

**Planned, not yet active:** trial **5/minute and 100/hour**; paid Pro **10/minute and 300/hour**. Once enabled, both limits apply per customer across their keys and IPs; reaching either triggers rate limiting. No effective date has been announced. Enterprise limits are contract-specific. See [API rate limits](/docs/api-rate-limits) for the complete policy.

Row caps are unchanged: **trialing** Pro users get up to **10 rows** per response; **paid** Pro unlocks up to about **10,000 rows** per response (server-configured). Prefer `LIMIT`, symbol, and date filters. Overly broad queries may return `QUERY_TOO_BROAD` or timeout.

**Q: When is the data updated (delay)?**  
A: The API enforces a minimum **15-minute** delay from each trade’s execution timestamp. At 10:00:00 ET, the newest visible trade is from 09:45:00 ET or earlier. For live prints, use the Realtime WebSocket API.

**Q: How far back does historical data go?**  
A: Trade history starts **2025-02-18** and covers each subsequent U.S. options session (2.8B+ trades and growing). Coverage adds about one trading day after each session settles.

**Q: What is typical daily volume on the related realtime stream?**  
A: On the order of **10M+** prints/day overall. AGGREGATED mode ~**4M** records/day; RAW mode ~**7M** records/day. Historical SQL stores the raw tape.

**Q: Is the historical option data modified or aggregated?**  
A: **No** — only raw, unmodified prints (unlike realtime AGGREGATED mode). Aggregate yourself with `SUM` / `COUNT` / `GROUP BY` when needed.

**Q: How does the free trial work? Can it be extended?**  
A: Complete qualification with an invitation code, then explicitly activate the **14-day** no-card Pro trial when you are ready. It includes Historical SQL (10-row cap), Realtime WebSocket, Option Chain, and Market Structure under one key. Eligible trial users can receive **50% off the first year**; the authenticated portal presents the conversion steps. Trials are **not auto-extended**.

**Q: What does the Pro plan include / refunds?**  
A: One Pro subscription covers all four products. Eligible trial users can receive **50% off the first year**, with next steps shown inside the authenticated portal. **30-day** money-back guarantee; cancel anytime. Refunds: email support@optiondata.io from your login address within 30 days of purchase.