7SQL and Data

Answer to Question 7.1.

Listing 7.12 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.

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

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.

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.

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.

Answer to Question 7.3.

It returns no rows at all, because blocked contains a NULL. Listing 7.13 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.

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

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.

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.

Answer to Question 7.5.

SQLite and PostgreSQL return 0. DuckDB returns 0.5. Listing 7.14 returns 0.5 on all three, and casting to double does too.

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

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.

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 has both queries.

-- 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;

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

Answer to Question 7.7.

SQLite happens to return the right prices, DuckDB rejects the query, and so does PostgreSQL. Listing 7.16 is correct on all three.

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;

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.

Answer to Question 7.8.

The three functions differ only on ties, and the ties decide who is kept. Listing 7.17 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.

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)

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.

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 fixes it by ordering on seq as well.

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

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.

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 fixes both.

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.

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;

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.

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.

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 (…).

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 returns the two bars.

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.

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;

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.

Answer to Question 7.13.

Listing 7.21 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)$.

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;

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<?).

Answer to Question 7.14.

Listing 7.22 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.

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;

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.

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.

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;

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.

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

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;

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.

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.

Answer to Question 7.18.

Listing 7.25 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.

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;

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.

Answer to Question 7.19.

Listing 7.26 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.

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;

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.

Answer to Question 7.20.

Listing 7.27 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.

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;

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.

Answer to Question 7.21.

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

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;

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;

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.

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 fixes the first, and Listing 7.31 finds the second.

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;

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;

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.

Answer to Question 7.23.

Listing 7.32 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.

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;

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.

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. Total profit here is 4000, and the query stays right when the position goes short.

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;

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.

Answer to Question 7.25.

Listing 7.34 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.

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;

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.