The Interview Book · Careers

27SQL and Data

An as-of join of trades to quotes returns more rows than there were trades. The analyst who wrote it says the join is correct because every trade matched a quote; the interviewer asks what happens when two quotes share a timestamp, and the duplicate rows explain themselves. SQL questions in research and data interviews test a handful of things: what a join does to row counts, how NULLs behave, window functions, time-based joins without look-ahead, and whether the candidate can express the same answer in an array language. The databases are One Quant Book 15’s (chapters 10 and 25) and the point-in-time discipline One Quant Book 7’s (chapter 3). Every query here runs in the chapter’s tests on DuckDB and, where the dialect allows, on SQLite, against generated tables with planted NULLs and a planted duplicate timestamp, and is compared with a pandas or numpy computation.

27.1 Joins and their cardinality

An inner join keeps the pairs of rows that match; a left join keeps every left row, with NULLs where nothing matches; an anti join (NOT EXISTS) keeps the left rows with no match. The number of rows a join returns is the sum over keys of the product of the matching rows’ counts, so a key that is not unique on the right multiplies left rows: the commonest silent bug in research data.

SELECT COUNT(*) AS rows_after_join, (SELECT COUNT(*) FROM quotes WHERE sym = 'AAA') AS aaa_quotes
FROM quotes q1 JOIN quotes q2 ON q1.sym = q2.sym AND q1.ts = q2.ts
WHERE q1.sym = 'AAA';

Listing 27.1. Joining a table to itself on (symbol, timestamp) exposes a duplicated key. code/interviews/27-sql-and-data/sql/duplicate_join.sql

Method 27.1 (Checking a join)

Before joining, count rows per key on each side; a key that should be unique and is not is a data defect.
After joining, compare the row count with the left table’s; for a many-to-one join they must be equal.
Count the NULLs a left join produced: they are the unmatched rows, and an anti join lists them.

NULL is not a value but the absence of one: COUNT(*) counts rows, COUNT(col) and AVG(col) ignore NULLs, and any comparison with NULL is unknown, so WHERE col <> 5 silently drops the NULL rows. WHERE filters rows before grouping; HAVING filters groups after aggregation.

27.2 Window functions

A window function (One Quant Book 15, chapter 10) computes over a set of rows related to the current one without collapsing them: rankings (ROW_NUMBER), running totals (SUM with an ordered window), neighbours (LAG, LEAD).

SELECT account, day, cum_pnl,
       cum_pnl - MAX(cum_pnl) OVER (PARTITION BY account ORDER BY day) AS drawdown
FROM (
    SELECT account, day, SUM(pnl) OVER (PARTITION BY account ORDER BY day) AS cum_pnl
    FROM pnl
) c
ORDER BY account, day;

Listing 27.2. Running P&L and drawdown per account: a running sum, then the distance from its running maximum. code/interviews/27-sql-and-data/sql/drawdown.sql

The “gaps and islands” family asks for runs of consecutive rows with a property. The standard trick: within the rows that have the property, the difference between a row’s position in time and its rank is constant along a run, so grouping by that difference groups each run.

SELECT account, MAX(run_length) AS longest_positive_run
FROM (
    SELECT account, grp, COUNT(*) AS run_length
    FROM (
        SELECT account, day, pnl,
               day - ROW_NUMBER() OVER (PARTITION BY account ORDER BY day) AS grp
        FROM pnl
        WHERE pnl > 0
    ) positive
    GROUP BY account, grp
) runs
GROUP BY account
ORDER BY account;

Listing 27.3. The longest run of consecutive positive-P&L days per account. code/interviews/27-sql-and-data/sql/islands.sql

27.3 As-of joins and time

An as-of join (One Quant Book 7, chapter 3) attaches to each event the latest record of another table at or before the event’s time: the quote in force when a trade printed, the fundamental data known on a date. Three details decide whether it is right. The inequality: quote.ts <= trade.ts includes a quote stamped at the same instant, which may or may not have been visible to the trade; < excludes it, and the choice should follow how the timestamps were produced. Ties: two quotes with the same timestamp make “the latest” ambiguous, and engines resolve it differently unless a tie-breaker (a sequence number) is given. Direction: joining to the next record instead of the previous one is look-ahead, and it makes any signal built on it look excellent.

SELECT t.ts, t.sym, q.bid
FROM trades t ASOF LEFT JOIN quotes q ON t.sym = q.sym AND t.ts >= q.ts
ORDER BY t.ts;

Listing 27.4. An as-of join in DuckDB. code/interviews/27-sql-and-data/sql/asof_duckdb.sql

SELECT t.ts, t.sym,
       (SELECT q.bid FROM quotes q
        WHERE q.sym = t.sym AND q.ts <= t.ts
        ORDER BY q.ts DESC, q.rowid DESC LIMIT 1) AS bid
FROM trades t
ORDER BY t.ts;

Listing 27.5. The same join in portable SQL with a correlated subquery and an explicit tie-breaker (SQLite’s rowid, the insertion order). code/interviews/27-sql-and-data/sql/asof_portable.sql

Example 27.2 (A tie decides the answer)

The fixtures contain two quotes for one symbol stamped at the same time, with bids 100.40 and 100.45, and a trade two time units later. DuckDB’s as-of join attaches 100.40 to the trade; the portable query, which breaks ties by insertion order, and a numpy search for the last quote at or before the trade attach 100.45. Neither engine is wrong: the data do not say which quote was in force.

27.4 Array-language versions

Array languages and dataframe libraries answer the same questions with vector operations: cumulative sums for running totals, searchsorted for as-of lookups, group-by for aggregation. Interviews for research roles often ask for both forms, because production pipelines are written in one and research in the other.

def asof_numpy(trades, quotes):
    """Last quote at or before each trade, per symbol, by searchsorted (ties: the last of equal timestamps)."""
    out = []
    for _, t in trades.iterrows():
        q = quotes[quotes["sym"] == t["sym"]]
        i = np.searchsorted(q["ts"].to_numpy(), t["ts"], side="right") - 1
        out.append(np.nan if i < 0 else q["bid"].to_numpy()[i])
    return np.array(out)

Listing 27.6. The as-of lookup with numpy’s binary search: the last quote at or before each trade, ties resolved to the last of the equal timestamps. code/interviews/27-sql-and-data/python/iv_sql.py

27.5 Question bank

Interview question 27.1 ★ researcher, developer • systematic fund

Trades reference accounts; 5 of 61 trades belong to accounts missing from the accounts table. How many rows do an inner join and a left join return, and how do you list the orphans?

Solution

Solution of Interview question 27.1.

The inner join returns 56 rows (the matched trades, accounts being unique), the left join 61 (every trade, with NULL desk for the five orphans); SELECT * FROM trades t WHERE NOT EXISTS (SELECT 1 FROM accounts a WHERE a.account = t.account) lists the five. A NOT IN would fail silently if the subquery could return a NULL.

What the interviewer is looking for: join semantics, the anti join, and the NOT IN trap.

Interview question 27.2 ★ researcher, mle • any

Of 61 trades, 2 have a NULL quantity and the others total 60 100. What do COUNT(*), COUNT(qty), SUM(qty) and AVG(qty) return?

Solution

Solution of Interview question 27.2.

61, 59, 60 100 and $60\,100/59 \approx 1\,018.64$ : aggregates ignore NULLs, so the average is over the 59 known quantities. Treating NULL as zero would give $60\,100/61 \approx 985.25$ , a different answer to a different question; say which one is meant.

What the interviewer is looking for: NULL semantics in aggregates, and the choice they force.

Interview question 27.3 ★ researcher, developer • bank

Return the desks whose total traded quantity exceeds 20 000. Why must the condition go in HAVING, not WHERE?

Solution

Solution of Interview question 27.3.

Join trades to accounts, group by desk, and filter with HAVING SUM(qty) > 20000; on the chapter’s data only the delta1 desk qualifies, with 41 000. WHERE runs before grouping, row by row, and cannot see an aggregate.

What the interviewer is looking for: the order of evaluation: rows, groups, then group filters.

Interview question 27.4 ★ researcher, mle • systematic fund

A quotes table has 81 rows for one symbol; Listing 27.1 joins it to itself on symbol and timestamp and returns 83 rows. What does that tell you?

Solution

Solution of Interview question 27.4.

The key (symbol, timestamp) is not unique: one timestamp carries two quotes, which match each other and themselves, four rows instead of two, hence $81 + 2 = 83$ . Any join of trades to quotes on that key will duplicate the trades there. Decide what the duplicate means (two updates in the same microsecond, a feed replay, a bad merge) and deduplicate or add a sequence number before joining.

What the interviewer is looking for: reading row counts as a data-quality test.

SELECT sym, ts, qty
FROM (
    SELECT sym, ts, qty, ROW_NUMBER() OVER (PARTITION BY sym ORDER BY qty DESC, ts) AS rk
    FROM trades
    WHERE qty IS NOT NULL
) ranked
WHERE rk <= 2
ORDER BY sym, rk;

Listing 27.7. The two largest trades per symbol. code/interviews/27-sql-and-data/sql/top2.sql

Interview question 27.5 ★★ researcher, developer • any

Return the two largest trades per symbol. Explain Listing 27.7, and what changes if you use RANK instead of ROW_NUMBER.

Solution

Solution of Interview question 27.5.

ROW_NUMBER numbers the trades of each symbol from the largest quantity down, breaking ties by time, and the outer query keeps numbers 1 and 2. RANK gives tied quantities the same rank, so a symbol with three trades tied for largest returns three rows (and skips rank 2); DENSE_RANK does not skip. Say which the question wants; the chapter’s result agrees with a pandas sort-and-head on the same data.

What the interviewer is looking for: window ranking functions and their tie behaviour.

Interview question 27.6 ★★ researcher, risk • multi-manager fund

From a table of daily P&L per account, compute each account’s running P&L and drawdown in SQL. On the chapter’s data the worst drawdowns are 8.9 and 32.3: how would you verify them outside SQL?

Solution

Solution of Interview question 27.6.

Listing 27.2: a running sum ordered by day, then the difference from its running maximum. Verify with a cumulative sum and cumulative maximum in pandas on the same table, as the chapter’s test does (worst drawdowns $-8.9$ and $-32.3$ ), and on a hand-made three-day example whose answer is obvious.

What the interviewer is looking for: running aggregates, and an independent check.

Interview question 27.7 ★★ researcher • systematic fund

Compute each trade’s return from the previous trade in the same symbol. What does the first trade of each symbol get?

Solution

Solution of Interview question 27.7.

px / LAG(px) OVER (PARTITION BY sym ORDER BY ts) - 1. The first trade of each symbol has no predecessor, so LAG returns NULL and so does the return; keep it NULL rather than zero. Order by a sequence number if timestamps can tie.

What the interviewer is looking for: LAG with the right partition and the NULL at the start.

Interview question 27.8 ★★ researcher, developer • market maker

Compute the volume-weighted average price per symbol per five-minute bucket, with timestamps in seconds. What do you do with trades whose quantity is NULL?

Solution

Solution of Interview question 27.8.

Bucket as (ts - ts % 300) / 300 (portable integer division), then SUM(px * qty) / SUM(qty) per symbol and bucket. Exclude NULL quantities explicitly and report how many trades were excluded: a VWAP silently computed on a subset is wrong in a way nobody sees. The chapter’s DuckDB, SQLite and pandas answers agree.

What the interviewer is looking for: bucketing without dialect traps, and explicit treatment of NULLs.

Interview question 27.9 ★★ researcher, risk • multi-manager fund

Find each account’s longest run of consecutive days with positive P&L. Explain the trick in Listing 27.3.

Solution

Solution of Interview question 27.9.

Among the positive days, day - ROW_NUMBER() is constant along a run of consecutive days and changes when a day is missing, so grouping by it groups runs; count per group and take the maximum per account: 10 and 7 on the chapter’s data, which a loop over the days confirms. It assumes one row per account and day with no missing days in the calendar; with trading days, number the calendar first.

What the interviewer is looking for: the gaps-and-islands identity and its calendar assumption.

Interview question 27.10 ★★★ researcher, developer • market maker

Attach to every trade the bid in force when it printed. Compare Listings 27.4 and 27.5, and explain why they disagree on one trade of the chapter’s data.

Solution

Solution of Interview question 27.10.

Both attach the latest quote at or before each trade, per symbol. They disagree only on the trade two units after the duplicated timestamp: DuckDB’s as-of join picks 100.40, the portable query with its insertion-order tie-breaker picks 100.45 (Example 27.2). The data do not define which quote was in force; add the feed’s sequence number and order by it. Also say whether a quote stamped at the trade’s own time counts (<= or <).

What the interviewer is looking for: as-of semantics, ties and the equality case.

Interview question 27.11 ★★★ researcher, mle • systematic fund

A colleague’s as-of join uses trade.ts <= quote.ts. What has it computed, how would a signal built on it look, and how do you detect the mistake from the output alone?

Solution

Solution of Interview question 27.11.

It attached the first quote at or after each trade: the future. A signal built on it (say, the trade’s price against the attached mid) predicts returns remarkably well, because it has seen them. Detect it from the output: the attached quote’s timestamp is after the trade’s for almost every row (56 of 61 on the chapter’s data, 4 more at equal times), which a one-line check of quote_ts > ts reveals; make that check part of the pipeline.

What the interviewer is looking for: recognising look-ahead and a mechanical check for it.

SELECT sym, ts AS halt_start, next_ts AS halt_end
FROM (
    SELECT sym, ts, state, LEAD(ts) OVER (PARTITION BY sym ORDER BY ts) AS next_ts
    FROM status
) s
WHERE state = 'HALTED'
ORDER BY sym, halt_start;

Listing 27.8. Halts from a status table. code/interviews/27-sql-and-data/sql/halts.sql

Interview question 27.12 ★★★ developer, risk • bank

A status table records each change of an instrument’s trading state. Return the halt intervals (Listing 27.8). What does the query assume, and which rows would break it?

Solution

Solution of Interview question 27.12.

For each state change, LEAD(ts) gives the next change of the same instrument; the rows whose state is HALTED give the halt intervals, from their time to the next change: $[100, 160)$ and $[400, 470)$ for one instrument, $[300, 360)$ for the other. It assumes that consecutive rows differ in state and that the table is complete: two consecutive HALTED rows would split one halt in two, and a halt still open at the end has a NULL end, which should be reported as open rather than dropped.

What the interviewer is looking for: LEAD for intervals and the data assumptions made explicit.

Interview question 27.13 ★★★ researcher, mle • systematic fund

Write the as-of join of trades to quotes in numpy, in pandas and in Polars, and say how you would check that all three agree with the SQL.

Solution

Solution of Interview question 27.13.

Numpy: per symbol, searchsorted of the trade times in the quote times with side=’right’, minus one (Listing 27.6). pandas: merge_asof on ts with by=’sym’ and direction=’backward’, both frames sorted by time. Polars: join_asof on ts with by=’sym’ and strategy=’backward’. Check by comparing all three with the SQL row by row on data that contain the hard cases (a trade before the first quote, a quote at the trade’s time, duplicated timestamps); the chapter’s test does this and finds agreement everywhere except at the tie, where the tie rule decides.

What the interviewer is looking for: three idioms for the same join, and a differential check on the hard cases.

Sources and further reading

DuckDB documentation, “ASOF join” and window functions (version 1.5); SQLite documentation, window functions (version 3.37).
One Quant Book 7, chapter 3 (point-in-time data and as-of joins); One Quant Book 15, chapters 10 and 25 (dataframes; databases and SQL).