# SQL for Trading Data

> Learn the SQL queries traders use most: filtering bars, aggregating trades into candles, joining fills to orders, window functions for returns and daily P&L.

Source: https://learn.tradelabsai.com/programming/sql-for-trading-data/  
Track: Programming and Data · Level: Intermediate · Updated: 2026-10-03  
Publisher: TradeLabs AI (https://tradelabsai.com). Education, not financial advice.  
Cite as: TradeLabs Learn, "SQL for Trading Data", https://learn.tradelabsai.com/programming/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.

| Table | Columns |
|---|---|
| bars | symbol, ts, open, high, low, close, volume |
| trades | symbol, ts, price, size |
| fills | fill_id, order_id, symbol, side, qty, price, fee, ts |

See [Database Design for Market Data](https://learn.tradelabsai.com/programming/database-design-for-market-data/) for how to design tables like these.

## Filtering and sorting

```sql
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](https://learn.tradelabsai.com/programming/timestamps-and-time-zones/).

## Aggregating trades into candles

```sql
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](https://learn.tradelabsai.com/programming/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.

```sql
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)](https://learn.tradelabsai.com/indicators/simple-moving-average/).

## Daily P&L from fills

```sql
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](https://learn.tradelabsai.com/markets/mark-to-market/).

**Example: Checking cash flow by hand**
On one day a trader buys 100 shares at $50.00 and sells them at $50.80, paying $1 in fees on each fill. The query sums 100 times $50.80, or $5,080, minus 100 times $50.00, or $5,000, giving $80, then subtracts $2 of fees for a net $78. Because the position is flat at the end of the day, cash flow equals realised P&L. If only 60 shares had been sold, the remaining 40 would need to be valued at the closing price before the day's P&L is complete.

## Joining orders and fills

```sql
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](https://learn.tradelabsai.com/orders/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](https://learn.tradelabsai.com/programming/data-storage/).

## 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](https://learn.tradelabsai.com/programming/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](https://learn.tradelabsai.com/programming/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](https://learn.tradelabsai.com/infrastructure/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](https://learn.tradelabsai.com/programming/database-design-for-market-data/).

## Continue learning

- Next lesson: [Database Design for Market Data](https://learn.tradelabsai.com/programming/database-design-for-market-data/)
- Previous lesson: [WebSocket Market Data Streams](https://learn.tradelabsai.com/programming/websocket-market-data-streams/)
- Related: [WebSocket Market Data Streams](https://learn.tradelabsai.com/programming/websocket-market-data-streams/): How WebSocket streams deliver live trades, quotes and order book updates. Learn subscriptions, heartbeats, reconnecting safely and handling gaps in Python.
- Related: [Database Design for Market Data](https://learn.tradelabsai.com/programming/database-design-for-market-data/): How to design database tables for bars, ticks, symbols, orders and fills. Learn keys, indexes, partitioning, data types and how to avoid common design mistakes.
- Related: [Redis and PostgreSQL for Trading](https://learn.tradelabsai.com/infrastructure/redis-and-postgresql-for-trading/): How Redis and PostgreSQL work together in trading systems: Redis for live prices, caches and state, PostgreSQL for durable orders, fills and history.
- Related: [Data Storage, Compression and Caching](https://learn.tradelabsai.com/programming/data-storage/): Compare ways to store market data: CSV, Parquet, HDF5, PostgreSQL, time series and columnar databases. Learn compression, partitioning and how to choose.
- Related: [NumPy and Pandas for Traders](https://learn.tradelabsai.com/programming/numpy-and-pandas-for-traders/): Learn the pandas and NumPy operations traders use most: loading price data, returns, rolling windows, resampling bars, joining assets and avoiding common traps.
- Related: [Trade Accounting and Reconciliation](https://learn.tradelabsai.com/industry/trade-reconciliation/): Reconciliation checks that internal records of trades, positions and cash match brokers, custodians and clearing houses. Learn the process, common breaks and fixes.