Hedge Inventory with Return Covariance
Reported by candidates from Millennium's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The data structure here isn't a table trick, it's a 3x3 covariance matrix and a tiny linear system. Millennium reported this SQL question in September 2026, and it reads like a quant problem wearing a query costume. You get prices, a three-row portfolio and one ridge parameter. You compute returns, build Sigma, solve for hedges on the liquid assets, and return one row. If you blank on the linear algebra in SQL, StealthCoder is the safety net running invisibly during the live OA.
The problem
A desk holds inventory in three assets and may add hedge quantities only in assets marked as liquid. Estimate return risk from the time-sorted price history, solve the ridge-regularized hedge, and report the remaining portfolio variance. Risk model Sort prices by period. Convert the price columns to IEEE-754 float64, then compute consecutive simple returns current_price / previous_price - 1. Build the sample covariance matrix Sigma of those return rows, using denominator return_count - 1. Sort portfolio by asset_order; rows must align with asset_1, asset_2, and asset_3. Convert the quantities to float64. Let q be the inventory vector and L the indices marked is_liquid. Solve (Sigma_LL + ridge * I) * h_L = -Sigma_LA * q in float64. Set hedge quantities outside L to 0. Let r = q + h. Compute residual variance as r^T * Sigma * r. Return exactly one row containing the three hedge quantities in price-column order and the residual variance. Tables prices: period PK (Integer), asset_1 (Decimal), asset_2 (Decimal), asset_3 (Decimal) portfolio: asset_order PK (Integer), asset (Text), quantity (Decimal), is_liquid (Boolean) parameters: config_id PK (Integer), ridge (Decimal) Constraints 3 <= prices.row_count <= 2000, and periods are unique. Every price is an exact decimal in [1, 1000000] with at most 6 fractional digits. After sorting by period, every consecutive price ratio for the same asset lies in [0.9, 1.1]. portfolio contains exactly three rows with unique orders 1, 2, and 3 and matching asset names asset_1, asset_2, and asset_3. Every portfolio quantity is in [-1000, 1000] with at most 6 fractional digits, and at least one portfolio row has is_liquid = true. parameters contains exactly one row with 0.0001 <= ridge <= 1. The ratio and ridge bounds keep each return in [-0.1, 0.1] and the ridge-regularized liquid system symmetric positive definite with 2-norm condition number at most 601. The return covariance and every reported value are finite. Numeric results are compared with absolute tolerance 0.000001.
Reported by candidates. Source: FastPrep
Pattern and pitfall
Break it into stages. First, order prices by period and use LAG to get consecutive returns, current / previous - 1, cast to float64. Second, compute the sample covariance of three return series with denominator n-1. That's six distinct values: three variances and three covariances, each from AVG-style sums or COVAR_SAMP. Third, pivot the portfolio into q1, q2, q3 and liquid flags. Fourth, solve (Sigma_LL + ridge*I) h_L = -Sigma_LA q. Liquid subsets can be 1, 2 or 3 assets, so you need cases for each, or Cramer's rule on at most a 3x3. Pitfall: the rows must stay aligned to asset order, and illiquid hedges must be exactly 0. Another trap is integer or decimal division, so cast early. Finally compute r^T Sigma r with r = q + h. Tolerance is 0.000001, so float64 is fine. If the matrix algebra in SQL stalls you live, StealthCoder is your hedge.
Memorize the pattern. If you can't, run StealthCoder. The proctor sees the IDE. They don't see what's behind it.
You can drill Hedge Inventory with Return Covariance 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. Made by an engineer who treats the OA as theater. If yours is tonight, you don't have time to grind. You have time to hedge.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Millennium's OA.
Millennium reuses patterns across OAs. Made by an engineer who treats the OA as theater. If yours is tonight, you don't have time to grind. You have time to hedge. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Hedge Inventory with Return Covariance FAQ
How hard is this Millennium SQL question really?+
The SQL itself is easy: LAG, a covariance, a pivot. The difficulty is the math. You must hand-solve a regularized linear system of size up to 3x3 inside a query, and handle every liquid-subset case without mistakes.
What's the trick to solving the hedge in SQL?+
Compute Sigma entries in a CTE, then write the solution in closed form. For one liquid asset it's a division. For two or three, use Cramer's rule or the explicit 2x2 and 3x3 inverse. Branch on the is_liquid flags with CASE expressions.
Which covariance denominator should I use?+
Sample covariance, so divide by return_count - 1. Note that return_count is the number of prices minus one, since each return needs a previous row. Using COVAR_SAMP over the LAG-derived returns gets this right if your dialect has it.
What mistakes lose points on this one?+
Integer or decimal division before casting to float64, forgetting to drop the first row where LAG is NULL, misaligning asset order, and giving nonzero hedge for illiquid assets. Also check the final row returns three hedges in order, then variance.
How do I prepare in 48 hours?+
Practice LAG-based returns, covariance via sums of products, and hand-solving 2x2 and 3x3 systems. Then write the whole query in CTE stages and test it on a small made-up dataset where you know the hedge answer.