← Back to all articles

SQL over market data: windows, SAMPLE BY and indicators as columns

2 October 2026 · EquationDB team

EquationDB has its own market query language, but plenty of questions are easier in SQL: grouping, joining, rolling statistics with exact window frames, resampling bars into candles. So EquationDB also speaks SQL, on the same engine and the same data. You do not export bars to another tool, and you do not give up the market-specific parts when you switch: any EquationDB indicator or fundamental is a column. rsi(14), sma(200), roc(63) and pe are computed by the engine as you select them.

This article is a tour of the SQL features that matter most for market data: finance aggregates, SAMPLE BY, window functions with named windows, QUALIFY, and CTEs and joins across symbols.

When a query is SQL

A statement goes through the SQL layer when it starts with WITH, DECLARE, CREATE or CALL, or when it is a SELECT … FROM that uses a SQL feature (GROUP BY, JOIN, OVER, CASE, DISTINCT, UNION, HAVING, QUALIFY, SAMPLE BY, OFFSET, a SQL function) or reads a SQL table such as bars, financials or events. Everything else is an ordinary EquationDB statement. You do not switch modes; you just write the query.

The main tables:

bars needs a symbol = 'X' or symbol IN (…) filter, and filters on symbol, timestamp and tf are pushed down into the engine, so only the data you ask for is read.

Finance aggregates, per symbol

Aggregates in EquationDB SQL include the usual count, avg, median and stddev, and finance-specific ones such as sharpe, max_drawdown, cagr, corr, beta and alpha:

SELECT symbol, sharpe(ret) AS sharpe, max_drawdown(close) AS mdd, cagr(close) AS cagr FROM bars WHERE symbol IN ('SPY', 'QQQ', 'TLT', 'GLD') AND timestamp >= '2021-01-01' GROUP BY symbol ORDER BY sharpe DESC

One row per symbol: the Sharpe ratio of daily returns, the maximum drawdown and the compound annual growth rate since 2021, best Sharpe first. Four asset classes compared in one statement.

SAMPLE BY: candles from bars

SAMPLE BY groups rows into calendar-aligned time buckets: 15m, 1h, 1d, 1w or 1M for months. Combined with first, max, min, last and sum, it turns finer bars into coarser candles:

SELECT timestamp, first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close, sum(volume) AS volume FROM bars WHERE symbol = 'AAPL' AND timestamp >= '2025-01-01' SAMPLE BY 1M

These are monthly candles built from daily bars. The same pattern works on intraday data. With timestamp IN '$today' and a 30-minute bucket, you get half-hour candles for the latest session from 1-minute bars. Any non-aggregated column other than the timestamp becomes a grouping key, so several symbols can be sampled at once:

SELECT timestamp, symbol, first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close, sum(volume) AS volume FROM bars WHERE symbol IN ('AAPL', 'MSFT') AND timestamp IN '$today' SAMPLE BY 30m FILL(PREV, PREV, PREV, PREV, 0)

FILL decides what happens to empty buckets, one mode per aggregate in order: NONE, NULL, PREV, LINEAR or a constant. Here, prices carry forward and volume is zero. Buckets align to the calendar by default; ALIGN TO FIRST OBSERVATION starts them at the first row instead, so hourly buckets begin at 09:30 rather than 09:00.

Window functions with named windows

Window functions cover any aggregate plus row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value and nth_value, with PARTITION BY, ORDER BY and ROWS BETWEEN n PRECEDING AND CURRENT ROW frames. When several columns share a frame, name it once:

SELECT timestamp, close, avg(close) OVER w20 AS sma20, stddev(ret) OVER w20 AS sd20, (close - avg(close) OVER w20) / stddev(close) OVER w20 AS z, close / max(close) OVER (ORDER BY timestamp) - 1 AS drawdown FROM bars WHERE symbol = 'TSLA' AND timestamp >= '2026-01-01' WINDOW w20 AS (ORDER BY timestamp ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) LIMIT -10

This gives a rolling 20-day mean, the rolling volatility of returns, a z-score of the close against its 20-day band, and the running drawdown from the high since January. LIMIT -10 returns the last ten rows in order, which is usually what you want from a time series.

Of course, EquationDB already maintains sma(20) as a built-in function, and you could simply select it. Window functions earn their place when you want something the function catalog does not have, or an exact frame you control.

QUALIFY: filter on a window result

WHERE runs before window functions, so it cannot see them. QUALIFY runs after, which makes "top N per group" a one-liner:

SELECT symbol, sector, roc(63) AS m3, rank() OVER (PARTITION BY sector ORDER BY roc(63) DESC) AS rk FROM top500 QUALIFY rk <= 2 ORDER BY sector, rk

These are the two strongest stocks in each sector by three-month momentum. FROM top500 gives one row per stock with current values, and roc(63) is computed by the engine, so this is a sector-neutral momentum screen written in plain SQL.

CTEs and joins across symbols

WITH names intermediate results. Each CTE runs once, in order, and later ones can read earlier ones. Joining two symbols' bars on time gives you cross-symbol statistics:

WITH s AS (SELECT timestamp, ret FROM bars WHERE symbol = 'NVDA' AND timestamp >= '2024-01-01'), m AS (SELECT timestamp, ret AS mkt FROM bars WHERE symbol = 'SPY' AND timestamp >= '2024-01-01') SELECT beta(s.ret, m.mkt) AS beta, alpha(s.ret, m.mkt) AS alpha_pct, corr(s.ret, m.mkt) AS corr FROM s JOIN m ON s.timestamp = m.timestamp

That is NVIDIA's beta, alpha and correlation against SPY since 2024, from one CTE per series and an equality join (which runs as a hash join). LEFT JOIN with ON or USING and CROSS JOIN are supported too. RIGHT and FULL joins are not, so swap the tables and use LEFT JOIN.

Mixing the two languages

Any EquationDB statement in parentheses is a table. FROM (GET …), FROM (EVENTS …) and FROM (ANALYZE …) all work, so you can screen or pull events with the market language and aggregate the result in SQL. In the other direction, SQL statements can be used inside scripts and procedures.

Limits

To keep queries predictable on shared hardware, a bars query reads up to 25 symbols and 20,000 bars per symbol, a join produces at most 2 million rows, and every query has a 180-second timeout. Correlated subqueries are not supported; window functions and LEFT JOIN usually express the same thing.

Where to go next

• SELECT (EquationDB SQL) for every clause on one page.
• SAMPLE BY, Window functions, WITH and JOIN.
• The tutorial SQL over bars, step by step.
• Execution order, for when a clause does not behave the way you expect.