Historical SQL Quickstart

Historical SQL accepts read-only SELECT queries over HTTPS. The endpoint applies server-owned row, byte, and execution-time limits, and every visible trade is delayed by at least 15 minutes. Paid (active) access covers only the past 15 days (360 elapsed hours, including weekends and holidays). Older trades are excluded before aggregation; trial access is unchanged. See the Historical API reference for the complete policy.

1. Start with a bounded query

This query filters by trading date and symbol, selects explicit columns, and limits output:

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

Filtering by date and symbol helps ClickHouse prune work. LIMIT caps returned rows, while OptionData's server-enforced read limits bound the scan itself. Bound a latest-date lookup the same way, or read the latest date from RawOptionTradesMaxDateOnlyMV: an unbounded SELECT max(date) FROM RawOptionTrades reads the whole table and returns QUERY_TOO_BROAD.

2. Send the request

Store the key in OPTIONDATA_API_KEY, then use the canonical bearer header:

curl -X POST https://www.optiondata.io/api/historical/sql \
  -H "Authorization: Bearer $OPTIONDATA_API_KEY" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  --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"

A successful response has status: "SUCCESS", a data array, and entitlement/limit information in meta.

Start from the documented REST endpoint, then use the bounded query shown below.

Trial responses are capped at 10 rows even when the SQL uses a larger LIMIT.

3. Keep queries safe

• Query only published, whitelisted tables and columns.
• Filter by date and symbol before widening the request. Paid queries must target the past 15 days; a range crossing that boundary includes only eligible trades.
• Select only the columns you need.
• Include a reasonable LIMIT.
• Do not include semicolons, SQL comments, mutations, UNION, or query-level SETTINGS.

The public Historical Option Trades API reference is the schema source of truth for this restricted endpoint; system-table discovery is not part of the customer query surface.

4. Recover from errors

For QUERY_TOO_BROAD, the response includes reason: "SCAN_LIMIT_EXCEEDED" and deterministic hints. Narrow the date range, add selective filters such as symbol, or split a large request into smaller time windows before retrying. Avoid automatically retrying the same unchanged query.

LIMIT is still recommended to cap returned rows, but it does not necessarily reduce the number of rows the server must scan. Do not treat a smaller LIMIT as the primary fix for QUERY_TOO_BROAD.