---
title: "SQL and Data"
book: "The Interview Book"
subject: quant
language: en
chapter: 27
exercises: 0
source: https://one-course.com/books/quant/18/en/chapter/27-sql-and-data
---

# Chapter 27 — SQL 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.

```sql
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).**

1. Before joining, count rows per key on each side; a key that should be unique and is not is a data defect.
2. After joining, compare the row count with the left table’s; for a many-to-one join they must be equal.
3. 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`).

```sql
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.

```sql
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.

```sql
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*

```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.

```python
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 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 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 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](#lst-iv-sql-and-data-dup) joins it to itself on symbol and timestamp and returns 83 rows. What does that tell you?

**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.*

```sql
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](#lst-iv-sql-and-data-top2), and what changes if you use `RANK` instead of `ROW_NUMBER`.

**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 of Interview question 27.6.**

[Listing 27.2](#lst-iv-sql-and-data-dd): 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 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 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](#lst-iv-sql-and-data-islands).

**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](#lst-iv-sql-and-data-asof) and [27.5](#lst-iv-sql-and-data-portable), and explain why they disagree on one trade of the chapter’s data.

**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](#ex-iv-sql-and-data-tie)). 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 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.*

```sql
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](#lst-iv-sql-and-data-halts)). What does the query assume, and which rows would break it?

**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 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](#lst-iv-sql-and-data-numpy)). 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).