# Historical Option Trades API (SQL)

> POST /api/historical/sql reference: paid 15-day history window, 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. Paid (`active`) subscriptions can query only the **past 15 days**, a rolling 360-hour window measured at query execution. Older trade rows are excluded before SQL expressions, joins, subqueries, and aggregations run, even when no date filter is supplied. A query spanning the boundary includes only eligible trade rows. An older-only row query returns an empty result; aggregate queries retain normal SQL empty-input behavior. Trial access and synthetic test-mode samples are unchanged.

## 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 stored individual trade records. Availability is subject to the access window and coverage limitations below.

**Notes:**
- Table names are case-sensitive: write `RawOptionTrades` exactly.
- 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') | Trade timestamp in America/New_York with millisecond storage capacity; older records may have only whole-second precision. 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 as a percent of the underlying's average daily share 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 in shares, unsigned, matching the realtime `dex` | `round(abs(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 WHERE symbol = 'AAPL' AND date >= today() - 7) 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 WHERE symbol = 'AAPL' AND date >= today() - 7) 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%20WHERE%20symbol%20%3D%20'AAPL'%20AND%20date%20%3E%3D%20today()%20-%207)%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 WHERE symbol = 'AAPL' AND date >= today() - 7) 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

For the latest trading date, use `RawOptionTradesMaxDateOnlyMV`. Otherwise bound every lookup by symbol and date: an unbounded `max(date)` over `RawOptionTrades` reads the whole table and returns `QUERY_TOO_BROAD`.

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

#### 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
  WHERE symbol = 'AAPL' AND date >= today() - 7
)
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
  WHERE symbol = 'AAPL' AND date >= today() - 7
)
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
  WHERE symbol = 'AAPL' AND date >= today() - 7
)
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 today() - INTERVAL 7 DAY AND today()
AND symbol = 'AAPL'
GROUP BY date, symbol, put_call
ORDER BY date DESC, total_premium DESC
LIMIT 50
```

## Review Response

This is an illustrative response with fixed sample timestamps, not a query for currently available trades.

### 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,
        "truncated": false,
        "minimum_data_delay_minutes": 15,
        "lookback_days": 15
    }
}
```

`meta.row_limit` is the most rows one response can return. `meta.truncated` is `true` when the query produced more rows than that and the extra rows were dropped; narrow the query or page through it. `meta.capped` only says that the trial row cap applies to your plan, not that rows were dropped.

COUNT, SUM, and other 64-bit integer results are returned as JSON strings (for example `"trades": "55973"`) so large values stay exact.

### Errors

Every error has `status: "ERROR"`, a stable `errorCode`, and a human-readable `errorMsg`:

| Status | `errorCode` | Meaning |
|--------|-------------|---------|
| `400` | `INVALID_REQUEST` | The body is not valid JSON or form data |
| `400` | `INVALID_SQL` | The SQL was rejected by the guardrails (not a single `SELECT`, a forbidden keyword, a comment, or a table that is not allowed) |
| `401` | `UNAUTHORIZED` | Missing or invalid API key |
| `403` | `SUBSCRIPTION_REQUIRED` | No active or trialing subscription |
| `405` | `METHOD_NOT_ALLOWED` | Use `POST`; the `Allow` header lists the supported method |
| `422` | `INVALID_QUERY` | The query could not be analyzed; see `reason` |
| `422` | `QUERY_TOO_BROAD` | The query exceeded the scan limits; see `reason` and `hints` |
| `429` | `RATE_LIMITED` | Rate limited; honor `Retry-After` |
| `504` | `QUERY_TIMEOUT` | The query timed out |
| `500` | `INTERNAL_ERROR` | Unexpected server error |

**Invalid API Key (401)**
```json
{
  "status": "ERROR",
  "errorCode": "UNAUTHORIZED",
  "errorMsg": "Unauthorized"
}
```

**SQL rejected by the guardrails (400)**
```json
{
  "status": "ERROR",
  "errorCode": "INVALID_SQL",
  "errorMsg": "Only single 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 (422 Unprocessable Content)**
```json
{
  "status": "ERROR",
  "errorCode": "QUERY_TOO_BROAD",
  "reason": "SCAN_LIMIT_EXCEEDED",
  "hints": [
    "Narrow the date range.",
    "Filter by symbol and other selective columns before widening the query.",
    "Split large requests into smaller time windows.",
    "Do not automatically retry the same unchanged query.",
    "Use LIMIT to cap returned rows; LIMIT does not necessarily reduce rows scanned."
  ],
  "errorMsg": "Query exceeded the historical-data scan limits. Narrow the query before retrying."
}
```

`QUERY_TOO_BROAD` means the SQL is valid but the request exceeded the server scan limit. Follow `reason` and `hints` instead of retrying the same query unchanged. Narrow the date range, add selective filters such as `symbol` (and expiry/strike when relevant), or split the request into smaller time windows. `LIMIT` is useful for controlling returned rows but does not necessarily reduce the amount of data scanned.

`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). `meta.truncated: true` means the result had more rows than that limit. Use `LIMIT` to cap output, and use symbol/date filters to reduce scanned data. Overly broad queries may return `QUERY_TOO_BROAD` or timeout.

**Q: How do I fix `QUERY_TOO_BROAD`?**
A: HTTP 422 `QUERY_TOO_BROAD` means the SQL is valid but the server scan limit was exceeded. Read the response's `reason` and `hints`, then narrow the date range, add selective filters such as `symbol`, or split the request into smaller time windows. Do not automatically retry the same unchanged query. A smaller `LIMIT` can reduce returned rows but does not necessarily reduce rows scanned.

**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: Paid subscriptions expose trades from the **past 15 days** only, inclusive of the lower timestamp boundary, with the newest 15 minutes excluded. This is 360 elapsed hours, not 15 trading sessions. Successful paid responses include `meta.lookback_days: 15`. The latest-date metadata table does not expose older trade rows.

**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: Historical SQL returns individual stored trade records without realtime trade aggregation. RAW does not mean an untouched exchange-event archive: records include normalized and calculated fields, and recovery or backfill can revise enrichment. You can aggregate the available rows with SUM, COUNT and GROUP BY.

**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 with a promotion code from Sales**; contact Sales for the code, then enter it on Billing before the trial ends. 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 a promotion code from Sales**, with application 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.


### Historical research and data quality

**Q: Do all historical trades retain original milliseconds, and how should I handle unusual times?**

A: No. The timestamp column supports milliseconds, but older records were stored at whole-second precision. Missing milliseconds cannot be recovered from those rows alone. Timestamp anomalies and missing coverage require separate verification; do not apply a blanket timezone offset. Contact support@optiondata.io with the symbol, date and a small example, without API credentials.

**Q: When is open interest updated?**

A: Open interest is updated on a T+1 basis: positions from trading day T become available on the next trading day. OI accompanying a trade generally reflects the prior trading day, not an intraday position count. Historical rows do not include an OI publication timestamp or revision identifier, so exact publication-before-execution timing is not certified for every row.

**Q: Are IV, Greeks and underlying prices point-in-time values? Does zero mean missing?**

A: These values are saved during trade processing; calculations and reference-price fallbacks may be used when information is unavailable. Historical rows do not expose every field observation time, calculation input or model version, and recovery/backfill may revise enrichment. They are not a certified point-in-time revision archive. Zero can represent an actual zero, missing OI or unavailable/unsuccessful enrichment; there is no universal zero-versus-missing distinction.

**Q: Does paid Pro add trade conditions, exchange, sequence IDs or contract history?**

A: No. Paid Pro uses the same historical schema. Historical RAW does not expose original conditions such as MLET/SLAN, exchange identifiers, original sequence IDs, correction/cancellation records, contract deliverables or historical ticker mappings. Do not assume those fields are available in a paid export; any separate offering requires written confirmation.

**Q: What can I reconstruct from historical RAW, and will it match AGGREGATED?**

A: You can calculate trade counts, total contracts and premium (price × size × 100 for standard contracts), infer price side from price/bid/ask, and group by contract and second. The aggregation rules use arithmetic average price/bid/ask, side recalculated from those averages, and maximum OI. Exact realtime parity is not guaranteed: missing prints, timestamp precision, inclusion rules and unavailable tie-order identifiers can change results. Omitted conditions cannot be reconstructed. Deliverables/multiplier metadata is absent, so the standard premium formula is not assured for adjusted contracts.

**Q: Does Pro include a complete, resumable full-history export?**

A: No. Standard paid SQL access is limited to the rolling 15-day window; a complete full-history export is not included. Coverage gaps and delisted-symbol completeness are not universally certified. Preserve identical-row multiplicity: a content hash is not a unique execution ID, and timestamp-only pagination can skip ties at a response limit. A separate export requires written scope, coverage, resumption/checksum guarantees, throughput and pricing; do not treat request limits as a delivery SLA.

**Q: Can I keep raw data or detailed feature caches after cancellation?**

A: Under the standard Terms, raw Data must be deleted when the subscription terminates. Detailed feature caches are not automatically exempt. Qualifying derived outputs must not recreate the original Data or a reasonable substitute. Query access and retention rights are separate; any extended retention requires a written agreement, and no separate retention-license price is published.

**Q: Can I publish derived signal probabilities without sharing raw data?**

A: Not automatically. Not publishing raw rows does not by itself establish publication rights. The Terms distinguish qualifying non-reconstructable derived outputs from Data and restrict public/commercial use. Contact support@optiondata.io with the proposed outputs and use for written clarification before publication.

See the [Terms of Service](/terms-of-service) for licensing and [API rate limits](/docs/api-rate-limits) for current and planned request limits. Customer-specific promotions do not expand data access or licensing rights.