Track the Book and Mark Final PnL
Reported by candidates from Millennium's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
Millennium reported this SQL question in September 2026, and the detail that trips people is the sign flip. A client BUY means the desk sells, so inventory drops and cash rises. A client SELL is the reverse. You need one row, one column called pnl, computed from fills and closing_prices. If you've got an OA coming, this is a conditional aggregation problem dressed up as finance. StealthCoder sits invisibly on your screen as a safety net if the join logic slips away mid-assessment, but the query itself is short once you see it.
The problem
A market-making desk fills client requests and tracks its cash and inventory. Every row in fills is one completed client trade. Book updates For a client BUY, the desk sells the quantity: desk inventory decreases and cash increases by quantity * fill_price. For a client SELL, the desk buys the quantity: desk inventory increases and cash decreases by quantity * fill_price. The closing_prices table represents the final price row, with exactly one closing mark for every traded asset. After all fills, compute pnl = cash + sum(inventory[asset] * mark_price[asset]). Return exactly one row with the column pnl. Tables fills: fill_id PK (Integer), asset (Text), client_side (Text), quantity (Integer), fill_price (Decimal) closing_prices: asset (Text), mark_price (Decimal) Constraints 0 <= fills.row_count <= 2000. Every fill_id is unique. Every client_side is exactly BUY or SELL. Every quantity is an integer in [1, 1000000]. Every fill and closing price is an exact decimal in (0, 1000000000000] with at most 6 fractional digits. closing_prices contains exactly one row for each asset that occurs in fills; it may also contain untraded assets. Each fill notional is at most 10^18; the absolute cash and marked-inventory totals are each at most 2 * 10^21, and |pnl| <= 4 * 10^21. These bounds keep every exact intermediate and result within the shared DECIMAL(38, 12) / 38-digit decimal runtime contract. Use exact decimal arithmetic and return 0 when there are no fills.
Reported by candidates. Source: FastPrep
Pattern and pitfall
Here's the trick. Note that pnl = cash + sum(inventory * mark). Per fill, the contribution is the same math: for a BUY, cash gains qty*price and inventory loses qty, so the marked value is qty*(price - mark). For a SELL it's qty*(mark - price). So join fills to closing_prices on asset, then SUM(CASE WHEN client_side = 'BUY' THEN quantity*(fill_price - mark_price) ELSE quantity*(mark_price - fill_price) END). No per-asset grouping needed, since the sum is linear. The pitfall is the empty table. SUM over zero rows returns NULL, so wrap it in COALESCE(..., 0). Also don't cast to float. Keep everything decimal, since the constraints demand exact arithmetic. An inner join is safe because every traded asset has exactly one mark. Untraded assets in closing_prices simply drop out. StealthCoder is your hedge if you blank on the sign handling live.
The honest play: practice the pattern, and have StealthCoder ready for the one you didn't see coming.
You can drill Track the Book and Mark Final PnL cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Built for the candidate who saw this exact problem leak two days before his OA and wondered if anyone had a play.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Millennium's OA.
Millennium reuses patterns across OAs. Built for the candidate who saw this exact problem leak two days before his OA and wondered if anyone had a play. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Track the Book and Mark Final PnL FAQ
How hard is this Millennium SQL question really?+
Easy to medium. There's no window function or recursion. The difficulty is getting the BUY/SELL signs right from the desk's point of view and handling the empty fills case. If you can write a join with a CASE inside SUM, you can solve it.
What's the trick to the pnl calculation?+
The math is linear, so you can skip per-asset inventory. Each BUY contributes quantity*(fill_price - mark_price). Each SELL contributes quantity*(mark_price - fill_price). Join to closing_prices once and sum everything in a single pass.
How do I return 0 when there are no fills?+
SUM over an empty set gives NULL, not 0. Wrap the aggregate in COALESCE(SUM(...), 0). Since you must return exactly one row, an aggregate with no GROUP BY always yields a row, so COALESCE covers the empty case.
Do I need to worry about decimal precision?+
Yes. The statement calls for exact decimal arithmetic, so don't cast to float or double. Multiply quantity by the decimal columns directly and let the engine keep the precision. Totals can reach about 10^21, which is why float would break.
Can I solve it with a CTE for cash and inventory separately?+
You can. Compute cash and per-asset net inventory in CTEs, join to marks, then add. It works but it's longer and easier to botch. The single SUM with CASE is shorter and less error-prone. Use the CTE version only if it helps you verify the answer.