Historical Option Trades API (SQL)

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

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

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

Derived Fields

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

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

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

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

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

SELECT max(date) AS latest_date
FROM RawOptionTradesMaxDateOnlyMV

Latest Symbol Trades

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

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

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

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)

{
    "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:

Invalid API Key (401)

{
  "status": "ERROR",
  "errorCode": "UNAUTHORIZED",
  "errorMsg": "Unauthorized"
}

SQL rejected by the guardrails (400)

{
  "status": "ERROR",
  "errorCode": "INVALID_SQL",
  "errorMsg": "Only single SELECT queries are allowed"
}

Invalid query fields or expressions (422 Unprocessable Content)

{
  "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)

{
  "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 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 for licensing and API rate limits for current and planned request limits. Customer-specific promotions do not expand data access or licensing rights.