---
title: "SQL and Data"
book: "Interviews III: Code"
subject: quant
language: en
chapter: 7
exercises: 0
source: https://one-course.com/books/quant/20/en/chapter/7-sql-and-data
license: CC-BY-NC-SA-4.0
credit: "One Quant Book, One Course (one-course.com)"
---

# Chapter 7 — SQL and Data

Writing a query that is right on the awkward rows, and reading one that looks right and is not: joins that lose or multiply rows, the three values of SQL logic, window frames, as-of joins, gaps and bad data. Every table is printed, so you can run the query in your head. A stem names the dialect where the answer depends on it.

**Question 7.1 ★ QD, Dev, DS, QR • any • junior.**

Here is a table `trades`, with `price` a double and `qty` an integer. Write a query giving, for each symbol and day, the volume-weighted average price, which is the sum of price times quantity divided by the sum of quantity. Include only symbol-days with more than 2 trades. What does it return here?

| symbol | day | price | qty |
| --- | --- | --- | --- |
| AAA | 2026-03-02 | 100.0 | 200 |
| AAA | 2026-03-02 | 102.0 | 100 |
| AAA | 2026-03-02 | 101.0 | 100 |
| BBB | 2026-03-02 | 50.0 | 400 |
| BBB | 2026-03-02 | 52.0 | 100 |
| AAA | 2026-03-03 | 104.0 | 100 |

**Answer to Question 7.1.**

[Listing 7.12](#lst-iv3-sq-vwap) groups by symbol and day and filters the groups with `HAVING`. It returns one row, AAA on 2 March at 100.75. Only that symbol-day has more than 2 trades.

```sql
SELECT symbol, day, SUM(price * qty) / SUM(qty) AS vwap
FROM trades
GROUP BY symbol, day
HAVING COUNT(*) > 2;
```

***Listing 7.12.** Volume-weighted average price, by symbol and day. code/interviews-3/07-sql-and-data/sql/vwap.sql*

| symbol | day | vwap |
| --- | --- | --- |
| AAA | 2026-03-02 | 100.75 |

The sum of price times quantity is $20\,000 + 10\,200 + 10\,100 = 40\,300$, and the sum of quantity is 400, so the result is 100.75. `WHERE` runs on rows before grouping, so it cannot see a count, and SQLite and DuckDB both reject `COUNT(*)` there. `HAVING` runs on the groups.

**Classic wrong answer.** `AVG(price)`. It gives 101 here, because it weights the 200-share trade the same as the 100-share ones.

**Follow-up.** *What if a symbol-day’s quantities can sum to zero, as with a correction?* Write `SUM(price * qty) / NULLIF(SUM(qty), 0)`, which gives NULL there. Without it, SQLite returns NULL, DuckDB returns infinity, or NaN if the numerator is zero too, as with a correction at the same price, and PostgreSQL raises an error, which ends the whole query. Three engines, three behaviours.

**Follow-up.** *And if `price` were an integer number of ticks?* Check the division: on SQLite, integer over integer truncates, and the result would be 100.

**Question 7.2 ★ QD, Dev, DS, QR • any • junior.**

The left table is `orders` and the right table is `fills`. You want every order, with its quantity filled on venue X, or NULL if it has none. What does each query in [Listing 7.1](#lst-iv3-sq-venue) return, and which is right?

| order_id | symbol |
| --- | --- |
| 1 | AAA |
| 2 | BBB |
| 3 | CCC |

| order_id | venue | qty |
| --- | --- | --- |
| 1 | X | 100 |
| 1 | Y | 50 |
| 2 | Y | 70 |

```sql
-- Query A
SELECT o.order_id, f.qty
FROM orders o LEFT JOIN fills f ON o.order_id = f.order_id
WHERE f.venue = 'X';

-- Query B
SELECT o.order_id, f.qty
FROM orders o LEFT JOIN fills f ON o.order_id = f.order_id AND f.venue = 'X';
```

***Listing 7.1.** Two left joins. code/interviews-3/07-sql-and-data/sql/venue_fills.sql*

**Answer to Question 7.2.**

Query A returns only one row, order 1 with 100. Query B returns all three orders, and it is the one you want.

| order_id | qty |
| --- | --- |
| 1 | 100 |
| 2 | NULL |
| 3 | NULL |

A left join keeps every row of the left table, filling the right side with NULLs where nothing matches, and then `WHERE` runs on the result. For orders 2 and 3, `f.venue` is NULL or Y, and `NULL = 'X'` is not true, so those rows are dropped: the `WHERE` has silently turned the outer join into an inner one. In query B the venue test is part of the match, so it decides which fills attach, and it cannot remove an order. Order 2 keeps its row with NULL, as its only fill is on Y.

The rule is that a condition on the right table of a left join belongs in `ON`.

**Classic wrong answer.** Adding `OR f.venue IS NULL` to query A. It brings back order 3, which has no fills at all, and still loses order 2, since that order’s row from the join says Y, not NULL. The query looks patched, and it is wrong for exactly the orders that traded elsewhere.

**Why it matters.** This bug changes a total without any error. A fill report that misses orders on other venues looks like a smaller, plausible number.

**Question 7.3 ★ QD, Dev, DS • any • junior.**

The left table is `orders` and the right table is `blocked`. What does [Listing 7.2](#lst-iv3-sq-notin) return, and why? Rewrite it so that it returns every order whose trader is not in the blocked list. Should order 3 be in your answer?

| order_id | trader |
| --- | --- |
| 1 | ann |
| 2 | bob |
| 3 | NULL |

| trader |
| --- |
| bob |
| NULL |

```sql
SELECT order_id
FROM orders
WHERE trader NOT IN (SELECT trader FROM blocked);
```

***Listing 7.2.** Orders from traders not blocked. code/interviews-3/07-sql-and-data/sql/not_in.sql*

**Answer to Question 7.3.**

It returns no rows at all, because `blocked` contains a NULL. [Listing 7.13](#lst-iv3-sq-notexists) is the fix: it returns orders 1 and 3. Whether order 3 belongs depends on what you mean: its trader is unknown, so it is not known to be blocked, and you must decide and say so.

```sql
SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM blocked b WHERE b.trader = o.trader);
```

***Listing 7.13.** The anti-join that NULLs cannot break. code/interviews-3/07-sql-and-data/sql/not_exists.sql*

SQL logic has three values. `'ann' NOT IN ('bob', NULL)` means ann is not bob, and ann is not NULL. The first part is true, and the second is unknown, as nobody knows what NULL stands for. True and unknown is unknown, and `WHERE` keeps only rows that are true. So one NULL in the list removes every row. SQLite and DuckDB both return that NULL, which you can check by selecting the expression.

`NOT EXISTS` asks whether a matching row exists, and that always has a yes or no answer. For order 3, `b.trader = o.trader` never holds, so no row exists, and the order is kept. Order 1 is kept in the same way, and order 2 finds bob and is dropped. To exclude unknown traders as well, add `AND o.trader IS NOT NULL`. Filtering the subquery with `WHERE trader IS NOT NULL` instead keeps `NOT IN` working, and it returns only order 1.

**Classic wrong answer.** “It returns order 1.” That is what the author expected. The NULL in the blocked list is what stops it, and it can appear in production long after the query was tested.

**Question 7.4 ★ QD, Dev, DS • any • junior.**

A table `quotes` has a double column `bid`, holding the five values 10.0, NULL, 20.0, 20.0, NULL. What do the two statements in [Listing 7.3](#lst-iv3-sq-counts) return, value by value? Which of the last two columns of the first statement is the average bid?

```sql
SELECT COUNT(*), COUNT(bid), COUNT(DISTINCT bid), SUM(bid), AVG(bid), SUM(bid) / COUNT(*)
FROM quotes;

SELECT COUNT(*) FROM quotes WHERE bid = NULL;
```

***Listing 7.3.** Counting with NULLs. code/interviews-3/07-sql-and-data/sql/counts.sql*

**Answer to Question 7.4.**

The first statement returns 5, 3, 2, 50, 16.67, 10, and the second returns 0. The fifth column, `AVG(bid)`, is the average of the known bids, 16.67. The sixth treats the unknown bids as zero, and whether that is right depends on what a NULL means here.

`COUNT(*)` counts rows. `COUNT(bid)` counts non-NULL values, and `COUNT(DISTINCT bid)` counts different non-NULL values: 10 and 20. `SUM` and `AVG` skip NULLs too, so the sum is 50 and the average is $50 / 3$, while `SUM(bid) / COUNT(*)` is $50 / 5$. `AVG(COALESCE(bid, 0))` also gives 10, and it says openly that a NULL counts as zero.

The second statement counts rows where `bid = NULL`. That comparison is unknown for every row, even the NULL ones, so nothing qualifies. The test for a missing value is `bid IS NULL`, which counts 2.

**Classic wrong answer.** “`COUNT(DISTINCT bid)` is 3, counting NULL as a value.” Aggregates skip NULLs, whereas `SELECT DISTINCT` and `GROUP BY` treat all NULLs as one value. That difference catches people out.

**Follow-up.** *What does `SUM` return on a table with no rows, or only NULLs?* NULL, not 0, so a report wants `COALESCE(SUM(x), 0)`. `COUNT` returns 0.

**Question 7.5 ★ QD, Dev, DS • any • junior.**

A table `fills` has two rows, with integer columns `filled` and `ordered` holding (30, 100) and (45, 50). What does [Listing 7.4](#lst-iv3-sq-ratio) return on SQLite, on DuckDB and on PostgreSQL? Write a version that returns the same on all three.

```sql
SELECT SUM(filled) / SUM(ordered) AS ratio FROM fills;
```

***Listing 7.4.** The share of the order filled. code/interviews-3/07-sql-and-data/sql/fill_ratio.sql*

**Answer to Question 7.5.**

SQLite and PostgreSQL return 0. DuckDB returns 0.5. [Listing 7.14](#lst-iv3-sq-ratiofixed) returns 0.5 on all three, and casting to double does too.

```sql
SELECT SUM(filled) * 1.0 / SUM(ordered) AS ratio FROM fills;
```

***Listing 7.14.** The same ratio, with a non-integer in it. code/interviews-3/07-sql-and-data/sql/fill_ratio_fixed.sql*

The sums are 75 and 150, both integers. In SQLite and PostgreSQL, integer divided by integer truncates, so $75 / 150$ is 0: PostgreSQL’s documentation gives `5 / 2` as 2. DuckDB’s `/` is floating-point division, and its `//` is the integer one, so `7 / 2` is 3.5 there, where SQLite gives 3. The SQLite and DuckDB results were checked by running them, and the PostgreSQL result comes from its manual.

Multiplying by `1.0` first makes the numerator non-integer, and the order matters: `1.0 * (a / b)` has already truncated. A query moved between engines keeps running and silently changes its answer, so the portable habit is an explicit cast in every ratio.

**Classic wrong answer.** “SQL has one rule for division.” Each engine has its own. It is the classic reason a query gives a different number after a migration.

**Why it matters.** A fill ratio of 0 for all but fully filled orders looks like an odd business fact, and it may take days to be traced to the type of a column.

**Question 7.6 ★ QD, Dev, DS • any • junior.**

A feed sometimes sends a trade again, and sometimes corrects it by sending the same `trade_id` with a new quantity. In the table `trades` below, `seq` is the arrival order. List the IDs that repeat, and say whether their copies agree. Then give the total quantity per symbol, where the latest version of each trade wins. Would `SELECT DISTINCT` have given the same totals?

| seq | trade_id | symbol | qty |
| --- | --- | --- | --- |
| 1 | 1 | AAA | 100 |
| 2 | 2 | AAA | 200 |
| 3 | 2 | AAA | 200 |
| 4 | 3 | BBB | 50 |
| 5 | 3 | BBB | 60 |
| 6 | 4 | BBB | 70 |

**Answer to Question 7.6.**

IDs 2 and 3 repeat. The copies of 2 agree, and those of 3 do not. With the latest version winning, the totals are AAA 300 and BBB 130. `SELECT DISTINCT` would give BBB 180, because it keeps both versions of trade 3. [Listing 7.15](#lst-iv3-sq-dup) has both queries.

```sql
-- Which IDs repeat, and do the copies agree?
SELECT trade_id, COUNT(*) AS copies, COUNT(DISTINCT qty) AS versions
FROM trades
GROUP BY trade_id
HAVING COUNT(*) > 1;

-- Totals per symbol, where the latest version of each trade wins
WITH ranked AS (
    SELECT symbol, qty,
           ROW_NUMBER() OVER (PARTITION BY trade_id ORDER BY seq DESC) AS rn
    FROM trades
)
SELECT symbol, SUM(qty) AS total
FROM ranked
WHERE rn = 1
GROUP BY symbol
ORDER BY symbol;
```

***Listing 7.15.** Finding repeats, then keeping the latest. code/interviews-3/07-sql-and-data/sql/duplicates.sql*

| trade_id | copies | versions |
| --- | --- | --- |
| 2 | 2 | 1 |
| 3 | 2 | 2 |

| symbol | total |
| --- | --- |
| AAA | 300 |
| BBB | 130 |

`COUNT(DISTINCT qty)` is the useful extra column: a value of 1 means a harmless replay, and more than 1 means a correction or a data fault. `DISTINCT` removes only rows that match in every selected column, so it handles the replay and not the correction. `ROW_NUMBER` over each ID, in descending arrival order, keeps exactly one row per ID: BBB is then $60 + 70$.

**Classic wrong answer.** Keeping the first copy, or `MAX(qty)`. A correction can go either way, so only the arrival order can settle which is current.

**Follow-up.** *And if there is no arrival column?* Then the table cannot tell you. Rows in SQL have no order of their own, so you go back to the source, or you report the disagreement rather than pick one.

**Question 7.7 ★★ QD, Dev, DS, QR • any • junior, experienced.**

Here is a table `prices`. What does [Listing 7.5](#lst-iv3-sq-bare) do on SQLite, on DuckDB and on PostgreSQL? Write a query for the latest price of each symbol that is correct on all three.

| symbol | ts | price |
| --- | --- | --- |
| AAA | 1 | 100.0 |
| AAA | 3 | 101.0 |
| AAA | 2 | 99.0 |
| BBB | 5 | 50.0 |
| BBB | 4 | 51.0 |

```sql
SELECT symbol, MAX(ts) AS ts, price
FROM prices
GROUP BY symbol;
```

***Listing 7.5.** The latest price, perhaps. code/interviews-3/07-sql-and-data/sql/latest_bare.sql*

**Answer to Question 7.7.**

SQLite happens to return the right prices, DuckDB rejects the query, and so does PostgreSQL. [Listing 7.16](#lst-iv3-sq-latest) is correct on all three.

```sql
WITH ranked AS (
    SELECT symbol, ts, price,
           ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY ts DESC) AS rn
    FROM prices
)
SELECT symbol, ts, price
FROM ranked
WHERE rn = 1
ORDER BY symbol;
```

***Listing 7.16.** The latest row per group, with a window. code/interviews-3/07-sql-and-data/sql/latest_portable.sql*

| symbol | ts | price |
| --- | --- | --- |
| AAA | 3 | 101.0 |
| BBB | 5 | 50.0 |

In standard SQL, a selected column must be grouped or aggregated. `price` is neither, so it is a bare column: which row should it come from? DuckDB fails with a binder error, and PostgreSQL’s documentation says it refuses too. SQLite has a documented special case: with a single `MIN` or `MAX`, bare columns come from the row holding that extreme, which is why it gets this right. With two such aggregates, it no longer says which row. So the query works here by a rule of one engine, and it reads like a portable query.

The window version says what you mean. If two rows tie on the latest time, `ROW_NUMBER` picks one arbitrarily, so add a second sort key if that matters. DuckDB also offers shorter forms: `arg_max(price, ts)`, and `QUALIFY` to filter on a window directly. Both return the same here.

**Classic wrong answer.** “It takes the price from an arbitrary row in every engine.” Not on SQLite with one `MAX`. It is a real rule there, though not one to depend on.

**Follow-up.** *A join to a subquery of `MAX(ts)` per symbol?* Also correct and portable. It returns both rows on a tie, which is sometimes what you want.

**Question 7.8 ★★ QD, Dev, DS • any • junior, experienced.**

The table `pnl` has one row per trader. You are asked for the top 2 traders by profit on each desk. Run `ROW_NUMBER`, `RANK` and `DENSE_RANK` over `(PARTITION BY desk ORDER BY pnl DESC)` in your head: what does each give on this table? Which traders does a filter of 2 or less keep under each one? Which would you use, and what must you ask first?

| desk | trader | pnl |
| --- | --- | --- |
| rates | ann | 50 |
| rates | bob | 50 |
| rates | cid | 40 |
| rates | dan | 30 |
| fx | eve | 70 |
| fx | fay | 60 |
| fx | gus | 60 |

**Answer to Question 7.8.**

The three functions differ only on ties, and the ties decide who is kept. [Listing 7.17](#lst-iv3-sq-ranks) gives them side by side. A filter of 2 or less keeps two traders on each desk under `ROW_NUMBER`, adds gus under `RANK`, and adds gus and cid under `DENSE_RANK`.

```sql
SELECT desk, trader, pnl,
       ROW_NUMBER() OVER (PARTITION BY desk ORDER BY pnl DESC, trader) AS row_num,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense
FROM pnl
WINDOW w AS (PARTITION BY desk ORDER BY pnl DESC)
```

***Listing 7.17.** Three rankings. code/interviews-3/07-sql-and-data/sql/ranks.sql*

| desk | trader | pnl | row_num | rnk | dense |
| --- | --- | --- | --- | --- | --- |
| fx | eve | 70 | 1 | 1 | 1 |
| fx | fay | 60 | 2 | 2 | 2 |
| fx | gus | 60 | 3 | 2 | 2 |
| rates | ann | 50 | 1 | 1 | 1 |
| rates | bob | 50 | 2 | 1 | 1 |
| rates | cid | 40 | 3 | 3 | 2 |
| rates | dan | 30 | 4 | 4 | 3 |

`ROW_NUMBER` never repeats. It must break ties somehow, and if the order does not settle them, the choice is arbitrary and can change between runs, so the listing sorts on `trader` too. `RANK` gives ties the same number and leaves a gap after them, while `DENSE_RANK` leaves no gap, so a tie at the top pulls in a third person.

What must you ask first? What “top 2” means when two people tie: exactly two rows, or everyone at the top two places, or at the top two distinct values? For a bonus list, you would hardly drop gus for a coin flip, so `RANK` is the natural reading. For a page showing two rows, it is `ROW_NUMBER`, with a stated tie-break.

**Classic wrong answer.** Using `ROW_NUMBER` without a tie-break. The result looks fine, and it is not reproducible.

**Question 7.9 ★★ QD, Dev, DS, QT • any • junior, experienced.**

The table `fills` holds signed quantities for one symbol, where `seq` is the order in which fills occurred, and two fills may share a time `ts`. A short position is against the rules, so the query in [Listing 7.6](#lst-iv3-sq-position) checks for a negative running position. What does it print, and does it catch the breach? Fix it.

| seq | ts | qty |
| --- | --- | --- |
| 1 | 10 | 100 |
| 2 | 20 | $-150$ |
| 3 | 20 | 100 |
| 4 | 30 | $-50$ |

```sql
SELECT seq, ts, qty, SUM(qty) OVER (ORDER BY ts) AS position
FROM fills
ORDER BY seq;
```

***Listing 7.6.** A running position. code/interviews-3/07-sql-and-data/sql/position.sql*

**Answer to Question 7.9.**

The query prints positions of 100, 50, 50, 0, so it misses the breach: the true position after fill 2 is $-50$. [Listing 7.18](#lst-iv3-sq-posfixed) fixes it by ordering on `seq` as well.

```sql
SELECT seq, ts, qty,
       SUM(qty) OVER (ORDER BY ts, seq ROWS UNBOUNDED PRECEDING) AS position
FROM fills
ORDER BY seq;
```

***Listing 7.18.** A running total, one row at a time. code/interviews-3/07-sql-and-data/sql/position_fixed.sql*

| seq | printed by the query | true position |
| --- | --- | --- |
| 1 | 100 | 100 |
| 2 | 50 | $-50$ |
| 3 | 50 | 50 |
| 4 | 0 | 0 |

With `ORDER BY` and no frame, the default is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, and a `RANGE` frame includes every peer of the current row, meaning all rows with the same `ts`. So both fills at time 20 are summed together before either is shown, and the dip between them disappears. Sorting on a key that is unique, here `ts, seq`, leaves no peers. Writing `ROWS` says so openly, and it also spares the engine from looking for peers.

**Classic wrong answer.** Sorting the output by `seq` and thinking that is enough. The final `ORDER BY` only arranges the output. The window’s own `ORDER BY` decides what each sum includes.

**Follow-up.** *And if the table has no `seq`, and two fills share a time?* Then the order is not in the data, and no query can recover it. A position check needs a sequence number from the source of the fills.

**Why it matters.** A limit breached for a moment is still breached. Two fills in one millisecond are normal in a fast market, so this comes up.

**Question 7.10 ★★ QD, Dev, DS • any • junior, experienced.**

The left table is `orders` and the right table is `fills`. [Listing 7.7](#lst-iv3-sq-fanout) is meant to give, for each desk, the total quantity ordered and the total filled. What does it print, what is wrong with each number, and how do you fix it?

| order_id | desk | qty |
| --- | --- | --- |
| 1 | rates | 100 |
| 2 | rates | 50 |
| 3 | fx | 200 |

| order_id | filled |
| --- | --- |
| 1 | 60 |
| 1 | 40 |
| 3 | 150 |

```sql
SELECT o.desk, SUM(o.qty) AS ordered, SUM(f.filled) AS filled
FROM orders o JOIN fills f ON o.order_id = f.order_id
GROUP BY o.desk
ORDER BY o.desk;
```

***Listing 7.7.** Ordered and filled, by desk. code/interviews-3/07-sql-and-data/sql/fanout.sql*

**Answer to Question 7.10.**

It prints fx 200 and 150, then rates 200 and 100. The filled numbers are right. The rates ordered quantity is wrong: it should be 150, and it is off in two separate ways. [Listing 7.19](#lst-iv3-sq-fanoutfixed) fixes both.

| desk | ordered | filled |
| --- | --- | --- |
| fx | 200 | 150 |
| rates | 200 | 100 |

A join makes one row per matching pair. Order 1 has two fills, so its quantity of 100 appears twice, and is summed as 200. That is the fan-out. Order 2 has no fills, so the inner join loses it, and its 50 is gone. So two errors are at work, and here $100 + 100$ only looks like a plausible total. On other data they could partly cancel, which is worse.

```sql
WITH per_order AS (
    SELECT order_id, SUM(filled) AS filled
    FROM fills
    GROUP BY order_id
)
SELECT o.desk, SUM(o.qty) AS ordered, SUM(COALESCE(p.filled, 0)) AS filled
FROM orders o LEFT JOIN per_order p ON o.order_id = p.order_id
GROUP BY o.desk
ORDER BY o.desk;
```

***Listing 7.19.** Aggregate first, then join. code/interviews-3/07-sql-and-data/sql/fanout_fixed.sql*

| desk | ordered | filled |
| --- | --- | --- |
| fx | 200 | 150 |
| rates | 150 | 100 |

The cure is to bring each table to the grain of the question before joining. With one row per order on each side, the join cannot multiply anything. `LEFT JOIN` keeps unfilled orders, and `COALESCE` turns their missing fills into 0.

**Classic wrong answer.** `SUM(DISTINCT o.qty)`. It would make rates $100 + 50$ only if order 2 appeared, and on data where two orders have the same quantity it would drop one of them.

**Follow-up.** *How do you spot a fan-out quickly?* Compare `COUNT(*)` before and after a join, and check that the key is unique on the side you expect.

**Question 7.11 ★★ QD, Dev, DS, QR • any • junior, experienced.**

In the table `closes`, `day` is a calendar day number, and days 4 and 5 are a weekend with no row. What are the two columns of averages in [Listing 7.8](#lst-iv3-sq-moving), row by row? Which one is a three-day moving average?

| day | price |
| --- | --- |
| 1 | 10.0 |
| 2 | 20.0 |
| 3 | 30.0 |
| 6 | 60.0 |
| 7 | 90.0 |

```sql
SELECT day, price,
       AVG(price) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS by_rows,
       AVG(price) OVER (ORDER BY day RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS by_range
FROM closes
ORDER BY day;
```

***Listing 7.8.** Two moving averages. code/interviews-3/07-sql-and-data/sql/moving.sql*

**Answer to Question 7.11.**

The columns differ from the first row after the gap, as the table shows. `by_rows` averages the last three observations and `by_range` the last three calendar days, so `by_range` is the three-day average in the plain sense. Over the weekend, `by_rows` is a three-trading-day average.

| day | price | by_rows | by_range |
| --- | --- | --- | --- |
| 1 | 10.0 | 10.00 | 10.00 |
| 2 | 20.0 | 15.00 | 15.00 |
| 3 | 30.0 | 20.00 | 20.00 |
| 6 | 60.0 | 36.67 | 60.00 |
| 7 | 90.0 | 60.00 | 75.00 |

A `ROWS` frame counts rows, whatever their days. On day 6 it takes days 2, 3 and 6, and so it gives $(20 + 30 + 60) / 3$. A `RANGE` frame with a numeric offset works on the value of the sort key: it takes $\text{day} - 2 \le d \le \text{day}$. On day 6 that is days 4 to 6, so only one row qualifies; on day 7 it is days 5 to 7, so it gives $(60 + 90)/2$.

Neither is wrong, but they answer different questions. Trading-day averages use `ROWS` on a clean calendar. A window in time, such as the last 300 seconds, needs `RANGE`. In either case, the first rows average fewer than three values, so decide whether to print them or to require a full window with `COUNT(*) OVER (…)`.

**Classic wrong answer.** “Three preceding rows is three days.” A missing row, whether a holiday or a data fault, silently stretches a `ROWS` window further into the past.

**Follow-up.** *Can `RANGE` with an offset have several sort keys?* No. The offset is applied to one numeric or date value, and SQLite’s manual states the requirement of a single term.

**Question 7.12 ★★ QD, Dev, DS, QR • any • junior, experienced.**

[Listing 7.9](#lst-iv3-sq-barsbad) should turn the table `ticks` into one bar per minute, with open, high, low and close. How many rows does it return, and what are they? Why? Fix it.

| minute | seq | price |
| --- | --- | --- |
| 1 | 1 | 100.0 |
| 1 | 2 | 103.0 |
| 1 | 3 | 101.0 |
| 2 | 4 | 102.0 |
| 2 | 5 | 99.0 |

```sql
SELECT DISTINCT minute,
       FIRST_VALUE(price) OVER w AS open,
       MAX(price)         OVER w AS high,
       MIN(price)         OVER w AS low,
       LAST_VALUE(price)  OVER w AS close
FROM ticks
WINDOW w AS (PARTITION BY minute ORDER BY seq)
ORDER BY minute, close;
```

***Listing 7.9.** Bars from ticks. code/interviews-3/07-sql-and-data/sql/bars_bad.sql*

**Answer to Question 7.12.**

It returns five rows, not two, one per tick. The frame ends at the current row, so `LAST_VALUE`, `MAX` and `MIN` see only the ticks so far. [Listing 7.20](#lst-iv3-sq-barsfixed) returns the two bars.

| minute | open | high | low | close |
| --- | --- | --- | --- | --- |
| 1 | 100.0 | 100.0 | 100.0 | 100.0 |
| 1 | 100.0 | 103.0 | 100.0 | 101.0 |
| 1 | 100.0 | 103.0 | 100.0 | 103.0 |
| 2 | 102.0 | 102.0 | 99.0 | 99.0 |
| 2 | 102.0 | 102.0 | 102.0 | 102.0 |

With `ORDER BY` in a window and no frame, the default frame runs from the start of the partition to the current row. So `LAST_VALUE` returns the current row’s price, which is rarely what anyone wants, and the running high and low change as you go. Each row then differs, and `DISTINCT` removes nothing. `FIRST_VALUE` is right by luck, since the frame always starts at the beginning.

```sql
SELECT DISTINCT minute,
       FIRST_VALUE(price) OVER w AS open,
       MAX(price)         OVER w AS high,
       MIN(price)         OVER w AS low,
       LAST_VALUE(price)  OVER w AS close
FROM ticks
WINDOW w AS (PARTITION BY minute ORDER BY seq
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY minute;
```

***Listing 7.20.** The whole partition in the frame. code/interviews-3/07-sql-and-data/sql/bars_fixed.sql*

| minute | open | high | low | close |
| --- | --- | --- | --- | --- |
| 1 | 100.0 | 103.0 | 100.0 | 101.0 |
| 2 | 102.0 | 102.0 | 99.0 | 99.0 |

A simpler plan for bars is a `GROUP BY minute` with `MIN` and `MAX`, and with open and close taken by `ROW_NUMBER` in each direction, or by `arg_min` and `arg_max` on DuckDB.

**Classic wrong answer.** “It returns two rows, because `DISTINCT` collapses each minute.” `DISTINCT` compares whole rows, after the windows have run.

**Question 7.13 ★★ QD, Dev, DS, QR • any • junior, experienced.**

The left table is `trades` and the right table is `quotes`, with `ts` in seconds. For each trade, return the bid and ask of the latest quote at or before the trade, but treat a quote more than 10 seconds old as missing. Use a correlated subquery, not any special join syntax. What do you return for each trade here? What does your query cost with and without an index on `quotes.ts`?

| trade_id | ts |
| --- | --- |
| 1 | 2 |
| 2 | 5 |
| 3 | 12 |
| 4 | 30 |

| ts | bid | ask |
| --- | --- | --- |
| 3 | 99.9 | 100.1 |
| 10 | 100.3 | 100.6 |
| 12 | 100.4 | 100.7 |

**Answer to Question 7.13.**

[Listing 7.21](#lst-iv3-sq-asofc) finds, for each trade, the time of the latest quote in the allowed span, then joins to fetch the quote. Trade 1 has no quote yet, trade 2 uses the quote at 3, trade 3 the quote at 12, and trade 4 none, since 12 is too old. Without an index it costs $O(nm)$ for $n$ trades and $m$ quotes; with one, $O(n \log m)$.

```sql
WITH matched AS (
    SELECT t.trade_id, t.ts,
           (SELECT MAX(q.ts) FROM quotes q
             WHERE q.ts <= t.ts AND q.ts >= t.ts - 10) AS quote_ts
    FROM trades t
)
SELECT m.trade_id, m.quote_ts, q.bid, q.ask
FROM matched m LEFT JOIN quotes q ON q.ts = m.quote_ts
ORDER BY m.trade_id;
```

***Listing 7.21.** An as-of lookup with a staleness limit. code/interviews-3/07-sql-and-data/sql/asof_correlated.sql*

| trade_id | quote_ts | bid | ask |
| --- | --- | --- | --- |
| 1 | NULL | NULL | NULL |
| 2 | 3 | 99.9 | 100.1 |
| 3 | 12 | 100.4 | 100.7 |
| 4 | NULL | NULL | NULL |

This is an as-of join: each row takes the latest matching row at or before its time. Three edges are worth saying aloud. A quote at the very time of the trade counts, as with trade 3. The left join keeps trades without a usable quote, rather than dropping them. And a staleness limit stops a trade at 30 from being priced off a quote from 18 seconds ago, which the unlimited version would do. Note also that a quote at the same second may in fact have come after the trade: the data can only say what the timestamps say.

The cost is the subquery, which runs per trade. With no index, SQLite’s plan shows a bare `SEARCH q` for the subquery, which reads all the quotes per trade, and it builds an automatic index for the join back to `quotes`. With an index on `ts`, the subquery’s plan says `USING COVERING INDEX` with `(ts>? AND ts<?)`.

**Classic wrong answer.** Joining on `q.ts <= t.ts` and taking `MAX(q.bid)`. That gives the highest bid in the past, not the latest bid.

**Follow-up.** *Several quotes share a time?* Then the join returns several rows per trade, so the quotes need a tie-break, such as a sequence number.

**Question 7.14 ★★ QD, Dev, LLE, DS • market maker, crypto, hedge fund • junior, experienced.**

A table `msgs` has one integer column, `seq`, the sequence numbers received from a feed that should have no holes. Its rows are 1, 2, 3, 6, 7 and 11. Write a query that returns each missing range as a first and a last number. A shorter query, the largest minus the smallest plus one minus the row count, gives the number missing: why is that not enough? What can no query on this table tell you?

**Answer to Question 7.14.**

[Listing 7.22](#lst-iv3-sq-gaps) compares each number with the next one and returns the ranges 4 to 5 and 8 to 10. The count alone says 5 are missing, but not where, which is what you need to ask for a replay. No query can see a hole at the start or the end, since those numbers leave no trace in the table.

```sql
SELECT seq + 1 AS first_missing, next_seq - 1 AS last_missing
FROM (SELECT seq, LEAD(seq) OVER (ORDER BY seq) AS next_seq FROM msgs)
WHERE next_seq > seq + 1
ORDER BY first_missing;
```

***Listing 7.22.** Missing ranges, from each row’s successor. code/interviews-3/07-sql-and-data/sql/seq_gaps.sql*

| first_missing | last_missing |
| --- | --- |
| 4 | 5 |
| 8 | 10 |

`LEAD` gives each row the next value in order, and a gap is where the difference exceeds 1. The last row has a NULL successor, and `NULL > x` is not true, so it drops out. The query cost is one sort, and the sequence numbers are usually already in order.

The count formula has a second flaw: duplicates hide holes. On the rows 1, 2, 2, 3, 6, it gives 1, where the truth is 2, because the extra row cancels one of the missing. `COUNT(DISTINCT seq)` repairs the count. The `LEAD` query still gives the right range there, because a difference of 0 is not above 1.

**Classic wrong answer.** Generating every number from the smallest to the largest, and anti-joining. It works, but it costs memory proportional to the range, and on a feed with a billion messages that matters.

**Follow-up.** *How do you find a hole at the end?* From outside the table: the feed’s heartbeat or the end-of-session message states the last sequence number sent. Compare with that.

**Question 7.15 ★★ QD, Dev, DS, QR • market maker, crypto, hedge fund • junior, experienced.**

The table `quotes` below holds one symbol, in arrival order `seq`. Write one query that returns each suspect row and one reason for it, checking in this order: bid at or above ask; a size of zero or less; a time that does not increase; and a mid-price that moved more than 5% from the previous row’s. Which rows come back? Which of them is a good row? How would you stop one bad print from flagging two rows?

| seq | ts | bid | ask | bid_size |
| --- | --- | --- | --- | --- |
| 1 | 1 | 100.0 | 100.2 | 500 |
| 2 | 2 | 100.1 | 100.3 | 400 |
| 3 | 3 | 100.4 | 100.2 | 300 |
| 4 | 4 | 100.1 | 100.3 | 0 |
| 5 | 4 | 100.1 | 100.3 | 200 |
| 6 | 6 | 110.0 | 110.2 | 100 |
| 7 | 7 | 100.2 | 100.4 | 300 |

**Answer to Question 7.15.**

Rows 3 to 7 come back, for the reasons in the table below. Row 7 is a good quote, flagged only because it returns from the bad print in row 6. To stop that, compare each mid with the median of the preceding rows rather than with the previous row.

```sql
WITH q AS (
    SELECT seq, ts, bid, ask, bid_size,
           LAG(ts) OVER (ORDER BY seq) AS prev_ts,
           LAG((bid + ask) / 2) OVER (ORDER BY seq) AS prev_mid
    FROM quotes
)
SELECT seq,
       CASE WHEN bid >= ask THEN 'crossed'
            WHEN bid_size <= 0 THEN 'size'
            WHEN ts <= prev_ts THEN 'time'
            WHEN ABS((bid + ask) / 2 - prev_mid) > 0.05 * prev_mid THEN 'jump'
       END AS problem
FROM q
WHERE bid >= ask OR bid_size <= 0 OR ts <= prev_ts
   OR ABS((bid + ask) / 2 - prev_mid) > 0.05 * prev_mid
ORDER BY seq;
```

***Listing 7.23.** One reason per suspect row. code/interviews-3/07-sql-and-data/sql/bad_ticks.sql*

| seq | problem |
| --- | --- |
| 3 | crossed |
| 4 | size |
| 5 | time |
| 6 | jump |
| 7 | jump |

`CASE` returns the first branch that holds, which gives the checking order. `LAG` needs a subquery because a window cannot appear in `WHERE`. A NULL from `LAG` on the first row makes those comparisons unknown, so it passes: the first row has nothing to be compared with. Row 5 is flagged on time, as its 4 is not above row 4’s 4.

The rows are compared with whatever came before, good or bad. Row 3 is crossed, yet its mid still enters the comparison for row 4, which can matter if a crossed quote is far away. The median cures both: a single print cannot move a median of five, so on DuckDB, where a window median exists, only row 6 is flagged for a jump, while rows 3, 4 and 5 keep their other reasons. SQLite 3.37 has no such function.

**Classic wrong answer.** Dropping every flagged row automatically. Row 7 is real, and a genuine gap on news can look like row 6: so review jumps, or check them against another source, before you delete them.

**Why it matters.** A strategy trained on data with one crossed quote or one wrong print can learn to trade on it, and the backtest will show a profit no one can earn.

**Question 7.16 ★★ QD, Dev, LLE, DS • any • junior, experienced.**

A table `latency` has one integer column `us`, holding 10, 12, 13, 15, 20, 22, 25, 30, 80 and 200. On DuckDB, what do `quantile_disc(us, 0.9)` and `quantile_cont(us, 0.9)` return, and why do they differ? SQLite 3.37 has neither, so write the 90th percentile there by the nearest-rank rule: the smallest value with at least 90% of the rows at or below it. Which definition would you report for latency?

**Answer to Question 7.16.**

`quantile_disc` returns 80 and `quantile_cont` returns 92, up to rounding: DuckDB 1.5.5 prints 91.99999999999996. [Listing 7.24](#lst-iv3-sq-rank) gives the nearest-rank value on SQLite: 80. For latency, report a value that really occurred, by a stated rule, because an interpolated number can land where no request ever was.

```sql
SELECT MIN(us) AS p90
FROM (SELECT us,
             ROW_NUMBER() OVER (ORDER BY us) AS pos,
             COUNT(*) OVER () AS n
      FROM latency)
WHERE pos * 10 >= n * 9;
```

***Listing 7.24.** The nearest-rank rule. code/interviews-3/07-sql-and-data/sql/nearest_rank.sql*

The discrete form returns an actual value: the smallest with at least 90% of the rows at or below it, which is the 9th of 10. The continuous form treats sorted values as points to interpolate between. Its position is $0.9 \times (10 - 1) = 8.1$, counting from 0, which is a tenth of the way from 80 to 200, so $80 + 0.1 \times 120 = 92$. No request took 92 microseconds: the gap between 80 and 200 is where the distribution has nothing.

In the listing, the test `pos * 10 >= n * 9` keeps the arithmetic in integers, which is exact, and it avoids `CEIL`, which not every SQLite build provides. It gives the same 80 on both engines.

The tail is where this matters most. With ten rows, the 90th percentile and the maximum are separated by one observation. A sample that small cannot say anything sure about the tail, and the 99th percentile of ten rows is simply the maximum.

**Classic wrong answer.** Averaging percentiles across venues or days. A percentile does not average. To combine, merge the raw data or the histograms, then read off the percentile.

**Follow-up.** *`NTILE(10)`?* It splits rows into ten nearly equal groups, and it can place equal values in different groups, so the boundary is not a clean percentile.

**Question 7.17 ★★ QD, Dev, DS • any • junior, experienced.**

On SQLite, a table `trades` has the index in [Listing 7.10](#lst-iv3-sq-plans), on `(symbol, ts)`. For each of the five queries, does the plan search the index or scan the table, and does it need a separate sort? Explain the rule behind your answers.

```sql
CREATE TABLE trades (symbol TEXT, ts BIGINT, price DOUBLE);
CREATE INDEX by_symbol_ts ON trades (symbol, ts);

-- Query A
SELECT price FROM trades WHERE symbol = 'AAA' AND ts >= 100;
-- Query B
SELECT price FROM trades WHERE ts >= 100;
-- Query C
SELECT price FROM trades WHERE symbol = 'AAA' ORDER BY ts;
-- Query D
SELECT price FROM trades WHERE symbol = 'AAA' ORDER BY price;
-- Query E
SELECT price FROM trades WHERE substr(symbol, 1, 1) = 'A';
```

***Listing 7.10.** One index, five queries. code/interviews-3/07-sql-and-data/sql/index_plans.sql*

**Answer to Question 7.17.**

Query A searches on both columns, with no sort. Query B scans. Query C searches on `symbol`, and the index supplies the order, so it needs no sort. Query D searches on `symbol` but needs a sort. Query E scans. The rule is that an index on `(symbol, ts)` is sorted by symbol and then by time, so it helps only from its leftmost column on, and only on the columns themselves.

SQLite’s `EXPLAIN QUERY PLAN`, read by the test, says:

- A: `SEARCH trades USING INDEX by_symbol_ts (symbol=? AND ts>?)` .
- B: `SCAN trades` . Times are sorted only within each symbol, so a condition on `ts` alone has no one place to start.
- C: `SEARCH` with `(symbol=?)` , and no sort step, as the rows for one symbol come out in time order.
- D: the same search, then `USE TEMP B-TREE FOR ORDER BY` .
- E: `SCAN trades` . A function of a column hides it from the index, even when the prefix is fixed. `symbol >= 'A' AND symbol < 'B'` can use it.

So write the index with the equality column first and the range or sort column next. SQLite can sometimes use an index without its first column, by a skip-scan, but only with statistics from `ANALYZE` and few distinct first values, so do not plan on it.

**Follow-up.** *The query reads only `ts` and `price`, and it is hot. What else can you do?* Add `price` to the index, as `(symbol, ts, price)`: the plan then says `COVERING INDEX`, as the table is never visited. Each extra index slows every write.

**Follow-up.** *Would you add one to a column store like DuckDB?* Usually not at first. It reads column chunks and skips them using each chunk’s minimum and maximum, so sorting the data on load often achieves what the index would.

**Question 7.18 ★★★ QD, Dev, DS, QR • any • junior, experienced.**

The table `daily` holds one row per trading day, in which `day` increases but need not be consecutive. Write a query that returns the first day, the last day and the length of the longest run of consecutive rows with positive profit. On ties, take the earliest. What does it return here? Use no loop or recursion.

| day | pnl |
| --- | --- |
| 1 | 5 |
| 2 | 3 |
| 3 | $-2$ |
| 6 | 1 |
| 7 | 2 |
| 8 | 4 |
| 9 | $-1$ |
| 10 | 7 |

**Answer to Question 7.18.**

[Listing 7.25](#lst-iv3-sq-islands) returns days 6 to 8, a run of 3. This is the gaps-and-islands method: a row’s position in the whole table, minus its position among the winning rows, is the same for every row of a run.

```sql
WITH numbered AS (
    SELECT day, pnl, ROW_NUMBER() OVER (ORDER BY day) AS pos
    FROM daily
),
wins AS (
    SELECT day, pos - ROW_NUMBER() OVER (ORDER BY day) AS island
    FROM numbered
    WHERE pnl > 0
)
SELECT MIN(day) AS first_day, MAX(day) AS last_day, COUNT(*) AS days
FROM wins
GROUP BY island
ORDER BY days DESC, first_day
LIMIT 1;
```

***Listing 7.25.** Islands, from a difference of row numbers. code/interviews-3/07-sql-and-data/sql/islands.sql*

Number all rows by day to get `pos`. Then keep the winners and number them again. Along a run of winners both numbers rise by 1 per row, so their difference stays fixed. Every losing row skipped raises `pos` and not the second number, so the difference jumps between runs. Here the winners are at positions 1, 2, 4, 5, 6, 8, the second numbering is 1 to 6, and the differences are 0, 0, 1, 1, 1, 2: three islands, of sizes 2, 3 and 1.

`pos` has to come from a first step on all rows. Using `day` itself would count a missing day as a break, whereas the question is about consecutive rows, and days 4 and 5 simply do not exist in this table. The test compares with a plain loop in Python on 100 random tables with holes in the days.

The tie rule goes in the final `ORDER BY`, and `LIMIT 1` takes the top. Note that `LIMIT` is not in every dialect, as some engines use `FETCH FIRST`.

**Classic wrong answer.** Using `LAG` alone. It tells you where a run starts, but not how long it is. You still need a running count of starts, which is the method of marking starts and summing them, in another form.

**Follow-up.** *The longest run of losses?* Change the filter. The same numbering of the whole table applies.

**Question 7.19 ★★★ QD, Dev, DS • any • junior, experienced.**

The table `events` holds a trader’s actions, with `ts` in seconds. A session is a run of one trader’s events in which each follows the last by 300 seconds or less, and a longer gap starts a new session. Write a query that returns, for each session, the trader, the session number, the first and last times and the number of events. What does it return here? Why is a gap of exactly 300 the case to test?

| trader | ts |
| --- | --- |
| ann | 100 |
| ann | 200 |
| ann | 700 |
| ann | 800 |
| ann | 1500 |
| bob | 150 |
| bob | 460 |
| bob | 760 |

**Answer to Question 7.19.**

[Listing 7.26](#lst-iv3-sq-sessions) marks each event that starts a session, then takes a running sum of the marks as the session number. It returns three sessions for ann and two for bob. A gap of exactly 300 is the case to test because the rule says “300 or less” continues a session, and bob’s last two events test exactly that.

```sql
WITH marked AS (
    SELECT trader, ts,
           CASE WHEN ts - LAG(ts) OVER (PARTITION BY trader ORDER BY ts) <= 300
                THEN 0 ELSE 1 END AS starts
    FROM events
),
numbered AS (
    SELECT trader, ts,
           SUM(starts) OVER (PARTITION BY trader ORDER BY ts) AS session
    FROM marked
)
SELECT trader, session, MIN(ts) AS first_ts, MAX(ts) AS last_ts, COUNT(*) AS n
FROM numbered
GROUP BY trader, session
ORDER BY trader, session;
```

***Listing 7.26.** Mark the starts, then sum them. code/interviews-3/07-sql-and-data/sql/sessions.sql*

| trader | session | first_ts | last_ts | n |
| --- | --- | --- | --- | --- |
| ann | 1 | 100 | 200 | 2 |
| ann | 2 | 700 | 800 | 2 |
| ann | 3 | 1500 | 1500 | 1 |
| bob | 1 | 150 | 150 | 1 |
| bob | 2 | 460 | 760 | 2 |

This pattern is worth knowing: turn a condition on neighbouring rows into a 0 or 1 column, and a running sum makes a group number. Partitions keep the two traders apart, so ann’s last event has no effect on bob’s first. A first event has no predecessor, so `LAG` gives NULL, the comparison is unknown, and `CASE` falls to `ELSE 1`, which correctly starts a session.

Bob’s gaps are 310 and 300. The first starts a new session, and the second continues one. Writing `< 300` would split that last pair, and only a test on the boundary would notice.

**Classic wrong answer.** Grouping on `ts / 300`. Fixed buckets are simple, but two events a second apart can fall in different buckets, and a long session is cut at every multiple of 300.

**Follow-up.** *Can this be a query you run every hour on new data?* Yes, but a session may span the boundary, so you must reprocess the last open session of each trader rather than start fresh.

**Question 7.20 ★★★ QD, Dev, DS, QR • any • junior, experienced.**

In the table `closes`, a NULL price means no trade that day. Fill each NULL with the last known price, and leave a NULL with nothing before it as it is. Write it for SQLite 3.37, which does not support `IGNORE NULLS`, and say how DuckDB can do it in one line. What do you return here? Why would you not fill backwards as well?

| day | price |
| --- | --- |
| 1 | NULL |
| 2 | 10.0 |
| 3 | NULL |
| 4 | NULL |
| 5 | 12.0 |
| 6 | NULL |

**Answer to Question 7.20.**

[Listing 7.27](#lst-iv3-sq-ffill) makes a group for each known price and spreads that price over its group. It returns NULL, 10, 10, 10, 12, 12. On DuckDB one line is enough: `LAST_VALUE(price IGNORE NULLS) OVER (ORDER BY day)`. Filling backwards would put a price from the future into the past, which in a backtest is look-ahead.

```sql
WITH groups AS (
    SELECT day, price, COUNT(price) OVER (ORDER BY day) AS grp
    FROM closes
)
SELECT day, MAX(price) OVER (PARTITION BY grp) AS filled
FROM groups
ORDER BY day;
```

***Listing 7.27.** Forward fill with a running count. code/interviews-3/07-sql-and-data/sql/ffill.sql*

| day | filled |
| --- | --- |
| 1 | NULL |
| 2 | 10.0 |
| 3 | 10.0 |
| 4 | 10.0 |
| 5 | 12.0 |
| 6 | 12.0 |

`COUNT(price)` skips NULLs, so its running value rises only at a known price. The groups by day are 0, 1, 1, 1, 2, 2. Each group holds at most one non-NULL price, so `MAX` over the group returns exactly that price. Group 0 has none, so day 1 stays NULL, as the question asks.

SQLite rejects the `IGNORE NULLS` syntax, and the test confirms it, so you need this trick there.

**Classic wrong answer.** `COALESCE(price, LAG(price) OVER (ORDER BY day))`. It fills only one step: day 4’s previous value is itself NULL, so it stays NULL.

**Follow-up.** *Fill at most one day, so that a stale price is not carried for ever?* Also keep `MAX(CASE WHEN price IS NOT NULL THEN day END) OVER (ORDER BY day)`, the last day with a price, and fill only when `day` minus that is at most 1. Here day 4 then stays NULL.

**Why it matters.** A filled price is a stale price, so a return computed across a fill is 0 and then a jump. For volatility and correlation, that can matter more than the gap itself.

**Question 7.21 ★★★ QD, Dev, DS, QR • market maker, hedge fund, crypto • experienced.**

The left table is `trades` and the right table is `quotes`, each with millions of rows per symbol, and neither has an index. For each trade, return the `mid` of the latest quote of the same symbol at or before the trade, or NULL if none. A quote at the same time as a trade counts. Give the query on DuckDB, using its special join. Then give a query with no such join and no correlated subquery, which runs on SQLite 3.37. What does each cost, and what do you return here?

| symbol | ts |
| --- | --- |
| AAA | 5 |
| AAA | 12 |
| BBB | 1 |
| BBB | 7 |

| symbol | ts | mid |
| --- | --- | --- |
| AAA | 3 | 100.0 |
| AAA | 12 | 101.0 |
| BBB | 2 | 50.0 |
| BBB | 9 | 51.0 |

**Answer to Question 7.21.**

On DuckDB, use `ASOF LEFT JOIN` ([Listing 7.28](#lst-iv3-sq-asofd)). The other query merges both tables and uses the running-count trick ([Listing 7.29](#lst-iv3-sq-asofu)). Each sorts, for about $O((n + m) \log(n + m))$, where a correlated lookup with no index costs $O(nm)$.

```sql
SELECT t.symbol, t.ts, q.mid
FROM trades t ASOF LEFT JOIN quotes q
  ON t.symbol = q.symbol AND t.ts >= q.ts
ORDER BY t.symbol, t.ts;
```

***Listing 7.28.** An as-of join on DuckDB. code/interviews-3/07-sql-and-data/sql/asof_duckdb.sql*

```sql
WITH merged AS (
    SELECT symbol, ts, 0 AS kind, mid FROM quotes
    UNION ALL
    SELECT symbol, ts, 1 AS kind, NULL FROM trades
),
grouped AS (
    SELECT symbol, ts, kind, mid,
           COUNT(mid) OVER (PARTITION BY symbol ORDER BY ts, kind
                            ROWS UNBOUNDED PRECEDING) AS grp
    FROM merged
)
SELECT symbol, ts, last_mid AS mid
FROM (SELECT symbol, ts, kind,
             MAX(mid) OVER (PARTITION BY symbol, grp) AS last_mid
      FROM grouped)
WHERE kind = 1
ORDER BY symbol, ts;
```

***Listing 7.29.** The same with a merged stream. code/interviews-3/07-sql-and-data/sql/asof_union.sql*

| symbol | ts | mid |
| --- | --- | --- |
| AAA | 5 | 100.0 |
| AAA | 12 | 101.0 |
| BBB | 1 | NULL |
| BBB | 7 | 50.0 |

Put quotes and trades in one stream, sorted by symbol, time and `kind`. Within a symbol, `COUNT(mid)` rises at each quote and holds at each trade. So a trade shares a group with exactly the latest quote before it, and `MAX(mid)` over that group is that quote’s mid. The last step keeps only the trades, by `kind = 1`.

`kind` is where the tie rule lives. Quotes are 0 and trades 1, so at equal times a quote comes first, as the question wants. To exclude a quote at the same moment, swap them.

The tests compare both queries with a brute-force search over 100 random tables. Quotes there have unique times per symbol. With duplicates, this query needs a further sort key, since nothing then says which of the two quotes counts last, and the result depends on the order in which the engine happens to read them.

**Classic wrong answer.** “An index would solve it.” It would cut each lookup to $O(\log m)$, but the question has none, and the merge does not need one.

**Follow-up.** *Which side is the outer?* In the DuckDB listing, trades are on the left, so a trade with no earlier quote stays, with NULL. A plain `ASOF JOIN` would drop it.

**Question 7.22 ★★★ QD, Dev, DS, QR • hedge fund, bank • experienced.**

A ticker can pass from one company to another. The left table, `master`, gives each instrument’s ticker and the dates it held it, from `valid_from` up to but not including `valid_to`, and the right table is `trades`. Dates are ISO text. [Listing 7.11](#lst-iv3-sq-pit) is a join someone wrote. What does it return, and what are the two separate problems with this result? Fix the query, and write a data-quality query that finds the bad rows in `master`.

| inst_id | ticker | valid_from | valid_to |
| --- | --- | --- | --- |
| 1 | ABC | 2020-01-01 | 2024-05-01 |
| 2 | ABC | 2024-05-01 | 9999-12-31 |
| 3 | XYZ | 2020-01-01 | 2023-01-01 |
| 4 | XYZ | 2022-06-01 | 9999-12-31 |

| ticker | day | qty |
| --- | --- | --- |
| ABC | 2024-04-30 | 10 |
| ABC | 2024-05-01 | 20 |
| XYZ | 2022-07-01 | 30 |

```sql
SELECT t.ticker, t.day, t.qty, m.inst_id
FROM trades t JOIN master m
  ON t.ticker = m.ticker AND t.day BETWEEN m.valid_from AND m.valid_to
ORDER BY t.day, m.inst_id;
```

***Listing 7.11.** A join on validity dates. code/interviews-3/07-sql-and-data/sql/pit_between.sql*

**Answer to Question 7.22.**

It returns five rows for three trades. The ABC trade on 2024-05-01 appears twice, because `BETWEEN` includes both ends, which is a fault in the query. The XYZ trade appears twice, because instruments 3 and 4 overlap in `master`, which is a fault in the data. [Listing 7.30](#lst-iv3-sq-pitfixed) fixes the first, and [Listing 7.31](#lst-iv3-sq-overlaps) finds the second.

| ticker | day | qty | inst_id |
| --- | --- | --- | --- |
| XYZ | 2022-07-01 | 30 | 3 |
| XYZ | 2022-07-01 | 30 | 4 |
| ABC | 2024-04-30 | 10 | 1 |
| ABC | 2024-05-01 | 20 | 1 |
| ABC | 2024-05-01 | 20 | 2 |

```sql
SELECT t.ticker, t.day, t.qty, m.inst_id
FROM trades t JOIN master m
  ON t.ticker = m.ticker AND m.valid_from <= t.day AND t.day < m.valid_to
ORDER BY t.day, m.inst_id;
```

***Listing 7.30.** A half-open join. code/interviews-3/07-sql-and-data/sql/pit_fixed.sql*

```sql
SELECT a.inst_id AS first_id, b.inst_id AS second_id, a.ticker
FROM master a JOIN master b
  ON a.ticker = b.ticker AND a.inst_id < b.inst_id
 AND a.valid_from < b.valid_to AND b.valid_from < a.valid_to;
```

***Listing 7.31.** Rows of the master that overlap. code/interviews-3/07-sql-and-data/sql/pit_overlaps.sql*

The dates mean a half-open span, from `valid_from` up to but not including `valid_to`, and `BETWEEN` is closed at both ends. On the handover day both ABC rows match, so an inner join multiplies the trade, and a volume report doubles it. The fixed query assigns that trade to instrument 2 only.

No query fix can settle XYZ, as the master itself claims two owners for June to December 2022. Two half-open spans overlap exactly when each starts before the other ends, and that one line is the check. It returns (3, 4), and it should run as a test whenever `master` is loaded.

ISO dates in text compare correctly as strings, which is why the comparisons work here, on SQLite and DuckDB. Other date formats do not.

**Classic wrong answer.** “Use `DISTINCT` to remove the duplicates.” That hides the double match, and it leaves one wrong instrument in the result.

**Follow-up.** *Why not join on today’s ticker for each instrument?* History then gets the wrong company, or none at all for a delisted one, which leaves out failed firms. This survivorship is why master data holds dates.

**Question 7.23 ★★★ QD, Dev, DS, QR • market maker, hedge fund, crypto • experienced.**

The table `trades` has an integer time `ts` in seconds, a double `price` and an integer `qty`. For each trade, give the volume-weighted average price over the five minutes ending at that trade, that is, over trades with $t - 300 < \text{ts} \le t$. Use a window function with a frame in time, not in rows. What do you return here? Two trades share the time 420: do they see each other? What must change if `ts` were a double?

| ts | price | qty |
| --- | --- | --- |
| 0 | 100.0 | 10 |
| 120 | 101.0 | 10 |
| 299 | 102.0 | 20 |
| 300 | 103.0 | 10 |
| 420 | 100.0 | 10 |
| 420 | 99.0 | 30 |

**Answer to Question 7.23.**

[Listing 7.32](#lst-iv3-sq-rvwap) uses a `RANGE` frame on `ts`, returning the table below. The two trades at 420 do see each other, as `RANGE` includes peers, so they get the same value. With a double `ts`, the integer shortcut for the open end no longer works.

```sql
SELECT ts, SUM(price * qty) OVER w / SUM(qty) OVER w AS vwap
FROM trades
WINDOW w AS (ORDER BY ts RANGE BETWEEN 299 PRECEDING AND CURRENT ROW)
ORDER BY ts, price;
```

***Listing 7.32.** A five-minute window, in time. code/interviews-3/07-sql-and-data/sql/rolling_vwap.sql*

| ts | vwap |
| --- | --- |
| 0 | 100.000 |
| 120 | 100.500 |
| 299 | 101.250 |
| 300 | 102.000 |
| 420 | 100.571 |
| 420 | 100.571 |

The window must be open at $t - 300$ and closed at $t$, and a `RANGE` frame is closed at both ends. With integer seconds, 299 preceding is exactly the same as the open end at 300: a trade at 0 is out of the window for the trade at 300, as the 102.000 shows. It is $(1010 + 2040 + 1030) / 40$, from the trades at 120, 299 and 300. At 420 the sum is over the trades at 299, 300 and both at 420: $7040 / 70 \approx 100.571$.

A running window means one pass over sorted rows, adding and removing as the frame moves, so a good engine does this in about $O(n \log n)$, mostly for the sort. Compare the self-join, which scales with the number of pairs inside the window.

For double times, no finite offset gives an open end exactly, and subtracting a tiny tolerance is a fudge. Convert the time to integer microseconds or nanoseconds first, and the same query is exact. That is also how you should store times.

**Classic wrong answer.** `ROWS BETWEEN 299 PRECEDING AND CURRENT ROW`. It counts 300 trades, not 300 seconds. On a quiet day it reaches back for hours, and on a busy one it covers a fraction of a second.

**Follow-up.** *Why divide two window sums, rather than average?* The average of price over a window weights each trade equally, where VWAP weights by quantity. A `WINDOW` clause shows that both sums use the same frame.

**Question 7.24 ★★★★ QD, Dev, DS, QT • any • experienced.**

The table `fills` holds one symbol’s buys and sells, in order `seq`, and the position never goes negative. Sales are matched to buys first in, first out: each sale uses up the oldest shares still held. In one SQL statement, with window functions but no loop and no recursion, return one row per matched pair of buy and sell, with the quantity and the profit, on SQLite 3.37 and on DuckDB. What is the total profit here? Does your query still hold if the position can go short?

| seq | side | price | qty |
| --- | --- | --- | --- |
| 1 | B | 100 | 100 |
| 2 | B | 110 | 50 |
| 3 | S | 120 | 120 |
| 4 | B | 90 | 30 |
| 5 | S | 130 | 60 |

**Answer to Question 7.24.**

Lay out all bought shares on one number line, and all sold shares on another: first in, first out means the $k$-th share sold is the $k$-th share bought. So each fill is a span, and the matched quantity is the overlap, as in [Listing 7.33](#lst-iv3-sq-fifo). Total profit here is 4000, and the query stays right when the position goes short.

```sql
WITH spans AS (
    SELECT seq, side, price,
           SUM(qty) OVER (PARTITION BY side ORDER BY seq) - qty AS lo,
           SUM(qty) OVER (PARTITION BY side ORDER BY seq) AS hi
    FROM fills
),
lots AS (
    SELECT b.seq AS buy_seq, s.seq AS sell_seq, b.price AS buy_price, s.price AS sell_price,
           CASE WHEN b.hi < s.hi THEN b.hi ELSE s.hi END
         - CASE WHEN b.lo > s.lo THEN b.lo ELSE s.lo END AS qty
    FROM spans b JOIN spans s
      ON b.side = 'B' AND s.side = 'S' AND b.lo < s.hi AND s.lo < b.hi
)
SELECT buy_seq, sell_seq, qty, (sell_price - buy_price) * qty AS pnl
FROM lots
ORDER BY sell_seq, buy_seq;
```

***Listing 7.33.** First-in first-out as overlapping spans. code/interviews-3/07-sql-and-data/sql/fifo.sql*

| buy_seq | sell_seq | qty | pnl |
| --- | --- | --- | --- |
| 1 | 3 | 100 | 2000 |
| 2 | 3 | 20 | 200 |
| 2 | 5 | 30 | 600 |
| 4 | 5 | 30 | 1200 |

A running sum per side gives each fill its span: buys cover $[0, 100)$, $[100, 150)$ and $[150, 180)$, and sales cover $[0, 120)$ and $[120, 180)$. Two half-open spans overlap when each starts before the other ends, and the shared length is the smaller end minus the larger start. `CASE` does that, as SQLite’s two-argument `MIN` means something else on DuckDB, where it returns a list, and `LEAST` is not in SQLite 3.37. The profit per pair is $(\text{sell} - \text{buy}) \times \text{qty}$. The test compares with a queue-based loop in Python on 150 random histories.

For shorts, the same reasoning holds. Whichever way the position crosses zero, first-in first-out on both sides still pairs the $k$-th share bought with the $k$-th share sold. The test confirms this on random histories that do go short. All that changes is how you read a row: when `sell_seq` comes before `buy_seq`, it is a short sale and its later cover.

Shares bought but not yet sold have no matching span, so they appear in no row. That is the open position, valued by marking to market, not here. With many symbols, add the symbol to the partition and to the join.

**Classic wrong answer.** “SQL needs a cursor or recursion, because lots are consumed one by one.” Thinking of positions on a number line removes the state altogether. The same trick does not work for last-in first-out, where the matching really depends on the path.

**Follow-up.** *And the cost?* The self-join on inequalities can touch many pairs without an engine that handles such joins well. A two-pointer walk in code is $O(n)$, so use this for a report rather than on a hot path.

**Question 7.25 ★★★★ QD, Dev, DS • market maker, crypto • experienced.**

The table `events` is the history of the buy side of one book: at step `seq`, the resting quantity at `price` changes by `delta`. In one SQL statement, on SQLite 3.37 and on DuckDB, return for each step the best bid after that step, meaning the highest price with a positive quantity, and the quantity there, or NULL if the side is empty. What do you return here? What does your query cost, and would you run it this way on a day of data?

| seq | price | delta |
| --- | --- | --- |
| 1 | 100 | 50 |
| 2 | 101 | 20 |
| 3 | 100 | 30 |
| 4 | 101 | $-20$ |
| 5 | 99 | 40 |
| 6 | 100 | $-80$ |

**Answer to Question 7.25.**

[Listing 7.34](#lst-iv3-sq-best) turns the history into versions of each price level, each valid from one step until the next change at that price, and takes the highest live level at each step. It returns the table below. On SQLite it costs $O(n^2)$ for $n$ events, so I would not run it on a day of data: replay in code instead.

```sql
WITH levels AS (
    SELECT price, seq AS from_seq,
           LEAD(seq) OVER (PARTITION BY price ORDER BY seq) AS to_seq,
           SUM(delta) OVER (PARTITION BY price ORDER BY seq) AS qty
    FROM events
),
ranked AS (
    SELECT e.seq, l.price, l.qty,
           ROW_NUMBER() OVER (PARTITION BY e.seq ORDER BY l.price DESC) AS rn
    FROM events e JOIN levels l
      ON l.from_seq <= e.seq AND (l.to_seq IS NULL OR e.seq < l.to_seq) AND l.qty > 0
)
SELECT e.seq, r.price, r.qty
FROM events e LEFT JOIN ranked r ON r.seq = e.seq AND r.rn = 1
ORDER BY e.seq;
```

***Listing 7.34.** The best bid at every step. code/interviews-3/07-sql-and-data/sql/best_bid.sql*

| seq | price | qty |
| --- | --- | --- |
| 1 | 100 | 50 |
| 2 | 101 | 20 |
| 3 | 101 | 20 |
| 4 | 100 | 80 |
| 5 | 100 | 80 |
| 6 | 99 | 40 |

Per price, the running `SUM(delta)` is the level’s quantity after each of its changes, and `LEAD(seq)` tells when that version ends. So a version is live over the half-open span from its `seq` to the next, or to the end if the next is NULL. Joining each step to the versions live then, with positive quantity, gives the book at that step. `ROW_NUMBER` by descending price picks the best. The outer left join brings back a step at which the side is empty, with NULLs. The tests compare with a dictionary replay on 100 random histories.

The join is the problem. Its output has about $n \times L$ rows, for $L$ live levels, and on SQLite, with no index for the inequality, finding them compares each step with every version, which is $O(n^2)$. Another engine may plan it better, but a replay in code with an ordered map costs $O(n \log L)$ and streams from disk. On a busy instrument with millions of events, and hundreds of levels, one is a query you run for a morning’s sample and the other is what you ship.

**Classic wrong answer.** Taking `MAX(price)` of events with positive `delta` so far. It forgets that a level can empty out, so after step 4 it would still say 101.

**Follow-up.** *How can you shrink the join?* A level only matters while it is near the top, so filter to prices within a few ticks of the best so far. Or run the query on a small time slice, per batch. Both help, but a replay is simpler.