SQL Questions That Appear in Quant Interviews

Window functions, self joins and point in time joins are the SQL patterns that dominate quant interview questions.

SQL questions in quant interviews test window functions, self joins for sequential comparisons, and aggregation over grouped time windows far more often than they test complex schema design, because the actual job of querying market data day to day is dominated by exactly those three patterns: ranking or comparing rows within a partition, comparing a row to the row before or after it, and rolling up trades or quotes into per-symbol, per-period summaries.

A very common question asks for the top three trades by size for each symbol on each day, which is a direct application of a window function that ranks rows within a partition defined by symbol and date, ordered by trade size descending, and then filters to the rows where the rank is three or less. Candidates who reach for a self join with a count of how many rows have a larger value, an approach that predates window functions in SQL, still get the right answer but demonstrate less fluency with the tool an interviewer expects a candidate to know.

A related question asks for a running total or a running average of a value within a partition, ordered by time, which uses a window function with an explicit frame clause rather than a plain aggregate, and the interviewer is often listening for the candidate to explain the difference between an aggregate function, which collapses a group into one row, and a window function, which returns one row per input row while still computing across a window.

Comparing a row to the previous row for the same symbol, for instance computing the change in price from one trade to the next, can be written as a self join on a condition that matches each row to the row immediately before it in time, or more idiomatically using the lag window function, which returns the value from a prior row without an explicit join at all. An interviewer will frequently ask for both approaches and ask which one performs better on a large table, and the expected answer is that the window function version generally performs better because it avoids the combinatorial cost of a join, which for a table with millions of rows can be substantial if the join condition is not…

A gap detection question, finding the largest interval between consecutive trades for a symbol, follows the same pattern: use lag to bring the previous timestamp onto the same row as the current one, subtract the two, and take the maximum of that difference, which is a two-line query once the lag pattern is recognized but a much longer one without it.