SQL and Window Functions for Market Data

GROUP BY collapses rows: give it a table of trades and ask for average price per symbol, and you get back one row per symbol — the individual trades are gone. But a lot of market-data questions want the opposite shape: keep every row, and attach a computed value to each one — this trade's 20-trade rolling average, this day's rank among the month, the price three rows ago for a lag comparison. Window functions compute an aggregate "over a window" of nearby rows without collapsing anything, which is why they are the workhorse of SQL-based market-data analysis.

The idea: a window is a per-row neighborhood, not a group

A window function has three parts: what to compute (AVG, SUM, RANK, LAG, ...), how to partition the rows (like GROUP BY, but non-collapsing — separate windows per symbol, say), and how to order and bound the window (e.g., "the 5 rows before this one, ordered by time"). The OVER (...) clause is what marks a function as a window function instead of a regular aggregate.

SELECT
  symbol,
  trade_date,
  price,
  AVG(price) OVER (
    PARTITION BY symbol
    ORDER BY trade_date
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
  ) AS rolling_5day_avg
FROM daily_prices;

In words: for each row, compute the average price over that same symbol's 5 most recent rows (this one plus the 4 before it), and attach it as a new column — the original rows are untouched, just enriched.

Worked example: rolling average and day-over-day change

Table daily_prices:

symbol	trade_date	price
ABC	1	100
ABC	2	102
ABC	3	101
ABC	4	105
ABC	5	103

SELECT symbol, trade_date, price,
  AVG(price) OVER (PARTITION BY symbol ORDER BY trade_date
                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day,
  price - LAG(price) OVER (PARTITION BY symbol ORDER BY trade_date) AS day_change
FROM daily_prices;

Row by row: trade_date=1 has no prior rows, so avg_3day = 100 (just itself) and day_change = NULL (nothing to lag from). trade_date=2: window is rows 1–2, avg_3day = (100+102)/2 = 101, day_change = 102-100 = 2. trade_date=3: window is rows 1–3, avg_3day = (100+102+101)/3 = 101, day_change = 101-102 = -1. trade_date=4: window is rows 2–4 (the 3-row cap slides forward), avg_3day = (102+101+105)/3 ≈ 102.67, day_change = 105-101 = 4. trade_date=5: window is rows 3–5, avg_3day = (101+105+103)/3 = 103, day_change = 103-105 = -2.

Five input rows, five output rows — nothing collapsed, each one enriched with its own local computation.

Where it shows up

Window functions are a near-guaranteed SQL interview question in quant-dev interviews: "write a query for the 20-day rolling volatility per symbol," "find the top-3 volume days per month per stock" (RANK() OVER (PARTITION BY symbol, month ORDER BY volume DESC)), or "compute each trade's price change from the previous trade" (LAG). Being fluent with PARTITION BY, ROWS BETWEEN, LAG/LEAD, and RANK/ROW_NUMBER/DENSE_RANK covers the large majority of what gets asked.

In production, window functions push rolling and ranking computations down into the database or query engine (Postgres, DuckDB, Snowflake, Spark SQL all support the same OVER syntax), which avoids pulling raw data into Python just to compute a moving average — for large tick or bar tables this is often an order of magnitude faster than the equivalent pandas groupby-apply, because the computation runs next to the data instead of after transferring it.