SQL for Quant Interviews: Joins, Groups, and Window Functions

Quant research runs on tabular data, and a surprising number of research screens include SQL — not exotic SQL, but fluent use of three ideas: aggregation, joins, and window functions. The third is where candidates separate.

Aggregation: GROUP BY and its gotcha

SELECT symbol, COUNT(*) AS trades, SUM(qty * price) AS notional
FROM trades
WHERE ts >= '2026-07-01'
GROUP BY symbol
HAVING SUM(qty * price) > 1e6
ORDER BY notional DESC;

The two rules that generate most interview corrections: every selected column is either grouped or aggregated; and WHERE filters rows before grouping, HAVING filters groups after — "symbols with over $1M traded" must be HAVING. Logical evaluation order (FROM → WHERE → GROUP → HAVING → SELECT → ORDER) explains every "why can't I use my alias here?" confusion in one line.

Joins: semantics, then row counts

INNER keeps matches; LEFT keeps all left rows with NULLs for misses; FULL keeps everything. The interview substance is in the edges:

• Row multiplication: joining trades to a quotes table with multiple quotes per key duplicates trade rows — the classic silent bug that inflates every downstream sum. Say "I'd check the join keys are unique on the right side" and you've preempted the trap.
• NULL logic: NULL = NULL is not true — it's NULL; use IS NULL. Anti-joins ("clients with no trades") via LEFT JOIN ... WHERE right.key IS NULL or NOT EXISTS.
• The quant-specific join is as-of (each trade matched to the latest quote at or before it — the point-in-time discipline from the backtesting lesson, in SQL form). Standard SQL does it awkwardly (correlated subquery / LATERAL); kdb+ and DuckDB have native asof joins — knowing the concept and that specialized tools exist is the expected answer.

Window functions: the difference-maker

Aggregates that don't collapse rows — each row sees a computation over its "window":

SELECT ts, symbol, price,
  LAG(price) OVER w                        AS prev_price,
  price / LAG(price) OVER w - 1            AS ret,
  AVG(price)  OVER (w ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS ma20,
  ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY price DESC)    AS price_rank
FROM prices
WINDOW w AS (PARTITION BY symbol ORDER BY ts);

Returns, moving averages, ranks, cumulative sums — the pandas operations of the Python lesson, executed in-database. The three families: ranking (ROW_NUMBER, RANK, NTILE — cross-sectional quantiles for the factor construction of the ML course), offsets (LAG/LEAD — returns and point-in-time comparisons), and framed aggregates (rolling windows). The canonical interview task — "top 3 trades by size per symbol" — is ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY qty DESC) wrapped in a subquery filtering rn <= 3; produce it without hesitation.

Performance, one paragraph

Enough to answer the follow-up: indexes make point lookups and joins fast at the cost of write overhead (B-trees — the sorted structures of the data-structures lesson); analytical stores (columnar: Parquet, ClickHouse, kdb+) scan compressed columns fast and are why research databases differ from trading databases; and EXPLAIN is how you check what the engine actually did rather than guessing. "Filter early, join on keys, let the optimizer work, verify with EXPLAIN" is a complete senior-sounding answer.

The interview version

"Daily P&L per desk, plus each desk's rank within its region, from a trades table." — One GROUP BY for the P&L, one window RANK() OVER (PARTITION BY region ORDER BY pnl DESC) over it, done in eight lines. The screen is testing whether SQL is a language you think in or one you look up — and window functions are the tell.