Client Markout Analysis
Reported by candidates from Millennium's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
Millennium reported this one in September 2026, and the first thing you'll notice is the sign convention. A BUY means the client bought and the desk sold, so markout is fill_price minus future_mid_price. A SELL flips it. It's a SQL question, not an algorithm one. You need a CASE expression, a GROUP BY on client_id, and a flag for negative means. If you have the OA in a day or two, this is a short, winnable query. If you blank on the sign logic mid-assessment, StealthCoder is the invisible safety net that reads the screen and hands you the query.
The problem
The trades table contains completed client trades and the corresponding mid-price at a fixed future measurement horizon. Calculate each client's average markout and identify clients whose flow lost money for the desk on average. Markout convention BUY means the client bought and the desk sold, so desk markout is fill_price - future_mid_price. SELL means the client sold and the desk bought, so desk markout is future_mid_price - fill_price. For each client, return the trade count, the unweighted arithmetic mean of per-trade markout, and is_invalid = true exactly when that mean is negative. Tables trades: trade_id PK (Integer), client_id (Text), side (Text), fill_price (Decimal), future_mid_price (Decimal) Constraints The table contains between 1 and 2000 trades. trade_id values are unique, and side is exactly BUY or SELL. The future mid-price is prejoined at the desk's fixed evaluation horizon; both prices are non-null positive exact decimals. Use an unweighted mean: every trade contributes once regardless of client, asset, or notional. A zero mean is valid and must not be flagged. Return one row per client in ascending client_id order. Numeric results are compared with absolute tolerance 0.000001.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is computing per-trade markout before you aggregate. Use CASE WHEN side = 'BUY' THEN fill_price - future_mid_price ELSE future_mid_price - fill_price END, then AVG over that per client_id. Add COUNT(*) for the trade count and (AVG(...) < 0) AS is_invalid. The common pitfalls are all small. Getting the sign backwards on one side. Using >= or <= so a zero mean gets flagged, when the statement says zero is valid. Weighting by anything, when the mean is explicitly unweighted. Casting to integers and losing decimals, when the tolerance is 0.000001. Forgetting ORDER BY client_id. You can wrap the CASE in a CTE or subquery to keep the AVG readable. With up to 2000 rows, performance is irrelevant. If the sign convention scrambles your head live, StealthCoder can give you the finished query as a hedge.
If this hits your live OA and you blank, StealthCoder solves it in seconds, invisible to the proctor.
You can drill Client Markout Analysis 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 by an Amazon engineer who would have shipped this the night before his JPMorgan OA if he'd had it.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Millennium's OA.
Millennium reuses patterns across OAs. Built by an Amazon engineer who would have shipped this the night before his JPMorgan OA if he'd had it. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Client Markout Analysis FAQ
How hard is the Millennium Client Markout Analysis question really?+
Easy to medium. There's no join and no window function. It's one CASE expression, one GROUP BY, and one boolean comparison. The difficulty is getting the BUY and SELL signs right and not flagging a zero mean. Write it slowly once and check it.
What's the trick to the markout calculation?+
Compute markout per trade first, then average. BUY gives fill_price minus future_mid_price. SELL gives future_mid_price minus fill_price. Both are from the desk's side. Then AVG that column grouped by client_id. Don't average prices separately and subtract afterward.
How do I handle the is_invalid flag correctly?+
Set it true only when the average is strictly negative, so use AVG(markout) < 0. The problem says a zero mean is valid and must not be flagged. Using <= is the classic mistake here. Return it as a boolean column.
Do I need to worry about NULLs or decimal precision?+
NULLs aren't an issue, since both prices are non-null and positive. Precision matters a bit. Keep the values as decimals and avoid integer casts or early rounding. The checker uses an absolute tolerance of 0.000001, so normal decimal math is fine.
How do I prepare for this in 48 hours?+
Practice CASE inside aggregates, GROUP BY with multiple aggregate columns, and boolean expressions in SELECT. Then write this exact query from scratch twice. Check ordering by client_id ascending and the strict negative comparison. That covers nearly everything this question tests.