SQL for Trading Data

SQL (Structured Query Language) is the standard language for asking questions of databases. Once trading data outgrows a few CSV files, it usually ends up in a database: price bars, raw trades, orders, fills and account snapshots. SQL lets you filter, aggregate and join that data quickly, often far faster than loading everything into memory. Even traders who do most of their analysis in pandas benefit from knowing enough SQL to pull exactly the slice of data they need.

Example tables#

The queries below use three simple tables.

See Database Design for Market Data for how to design tables like these.

Filtering and sorting#

SELECT ts, close, volume
FROM bars
WHERE symbol = 'AAPL'
  AND ts >= '2026-01-01' AND ts < '2026-04-01'
ORDER BY ts;

Use half open ranges (greater than or equal to the start, less than the end) for time filters. They never double count a boundary bar and work with any timestamp precision. See Timestamps, Time Zones and Daylight Saving.

Aggregating trades into candles#

SELECT symbol,
       date_trunc('minute', ts) AS minute,
       (array_agg(price ORDER BY ts))[1]      AS open,
       max(price)                             AS high,
       min(price)                             AS low,
       (array_agg(price ORDER BY ts DESC))[1] AS close,
       sum(size)                              AS volume
FROM trades
WHERE symbol = 'BTCUSDT'
GROUP BY symbol, minute
ORDER BY minute;

This PostgreSQL query turns raw trades into one minute bars. Open is the first trade price in each minute and close is the last. Other databases have their own functions for first and last values. See Tick Data and OHLCV Data.

Window functions for returns#

Window functions compute values across neighbouring rows without collapsing them, which is perfect for time series.

SELECT ts, close,
       close / lag(close) OVER (PARTITION BY symbol ORDER BY ts) - 1 AS ret,
       avg(close) OVER (PARTITION BY symbol ORDER BY ts
                        ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS sma20
FROM bars
WHERE symbol = 'AAPL'
ORDER BY ts;

lag returns the previous row's value, giving simple returns. The ROWS BETWEEN 19 PRECEDING AND CURRENT ROW frame gives a 20 bar moving average. See Simple Moving Average (SMA).

Daily P&L from fills#

SELECT date_trunc('day', ts) AS day,
       symbol,
       sum(CASE WHEN side = 'sell' THEN qty * price ELSE -qty * price END) - sum(fee) AS cash_flow
FROM fills
GROUP BY day, symbol
ORDER BY day, symbol;

This gives net cash flow per day and symbol, not true P&L, because open positions also need marking to market. See Mark-to-Market.

Joining orders and fills#

SELECT o.order_id, o.limit_price, avg(f.price) AS avg_fill, sum(f.qty) AS filled
FROM orders o
JOIN fills f ON f.order_id = o.order_id
GROUP BY o.order_id, o.limit_price;

Comparing the average fill price with the intended price measures slippage per order. See Slippage Analysis.

Performance tips#

• Index columns you filter by often, such as (symbol, ts).
• Select only the columns you need instead of SELECT *.
• Partition very large tables by time.
• Consider a time series or columnar database for billions of rows. See Data Storage, Compression and Caching.

Using SQL from Python#

pandas can run a query and return a DataFrame directly with pd.read_sql(query, connection), letting you pull a precise slice and continue analysis in Python. Use query parameters rather than building SQL strings by hand, which avoids errors and injection risks. See NumPy and Pandas for Traders.

Checking data quality with SQL#

SQL is also a quick way to audit data before trusting it. Count bars per symbol per day to find missing sessions, search for rows where the high is below the low or the close is outside the high and low range, and look for duplicate timestamps with a GROUP BY that keeps only counts above one. Running these checks after every load catches feed problems early. See Cleaning Market Data.

Frequently asked questions#

Do traders need to learn SQL?#

Not to trade manually, but anyone storing large amounts of price, trade or order data benefits from SQL for fast, precise queries.

Which database is best for trading data?#

PostgreSQL is a strong general choice; time series and columnar databases suit very large tick datasets. See Redis and PostgreSQL for Trading.

How do I calculate returns in SQL?#

Use the lag window function to get the previous price and divide the current price by it, then subtract one.

Next, learn how to design tables for market data in Database Design for Market Data.