Measure Liquidity
Reported by candidates from Millennium's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The structure this Millennium question hinges on is a window function over a wide table. Millennium reported it in September 2026, and it looks scarier than it is. You get a prices table with one column per asset and a requests table with asset names. You need one liquidity score per asset, with exact decimal math and tie-breaking rounding rules. It's SQL, so there's no algorithm to invent, just a clean pipeline of CTEs. If you blank on the order of steps mid-assessment, StealthCoder runs invisibly as a safety net while you work.
The problem
Use the prices and requests tables to calculate one liquidity score for each of Asset_1, Asset_2, and Asset_3. Metric definitions Sort prices by timestamp. Treat prices as exact decimal values. For each asset, calculate each adjacent return as current_price / previous_price - 1, round that return to 12 decimal places using round-half-away-from-zero for an exact tie, and then define volatility as the sample standard deviation of the rounded returns. Define request frequency as the number of requests for an asset divided by the total number of rows in requests. Min-max normalize the three volatility values together and the three frequency values together. If a metric has zero range, use 0.5 as that metric's normalized value for every asset. Liquidity score For each asset, calculate 0.1 + 0.9 * (0.5 * (1 - volatility_normalized) + 0.5 * frequency_normalized). Round the final score to 6 decimal places using the same round-half-away-from-zero rule and return rows in ascending asset-name order. Tables prices: timestamp PK (Timestamp), Asset_1 (Decimal), Asset_2 (Decimal), Asset_3 (Decimal) requests: request_id PK (Integer), asset (Text) Constraints The prices table contains at least 3 rows and at most 2000 rows. Every timestamp is unique, timezone-neutral, and uses whole-second YYYY-MM-DD HH:MM:SS precision. Every asset price is a strictly positive exact decimal value representable as DECIMAL(38, 12). The requests table contains at least 1 row and at most 2000 rows. Every request asset is exactly Asset_1, Asset_2, or Asset_3. Use sample standard deviation, equivalent to ddof=1 in Pandas. Return exactly three rows ordered by asset ascending.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is to unpivot first. Turn the wide prices table into rows of (timestamp, asset, price) using UNION ALL across Asset_1, Asset_2, Asset_3. Then use LAG(price) OVER (PARTITION BY asset ORDER BY timestamp) to get the previous price and compute price/prev - 1. Round to 12 places, then take STDDEV_SAMP per asset. Frequency is a COUNT per asset divided by COUNT(*) from requests. Min-max normalize with MIN and MAX window functions over the three rows, and wrap the divisor in a CASE so zero range returns 0.5. Pitfalls: float division instead of decimal, dropping the first row's NULL return, assets with zero requests vanishing from a plain GROUP BY (left join from the asset list), and ROUND ties. Standard ROUND may be half-even in some engines, so handle half-away-from-zero explicitly with SIGN(x) * FLOOR(ABS(x) * 10^n + 0.5) / 10^n. Order by asset at the end.
If you see this problem in your OA tomorrow, the play is to recognize the pattern in 30 seconds. StealthCoder buys you that recognition.
You can drill Measure Liquidity 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 passed his OA cold and still thinks the filter is broken.
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 passed his OA cold and still thinks the filter is broken. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Measure Liquidity FAQ
What's the core trick in the Millennium Measure Liquidity question?+
Unpivot the wide prices table into (timestamp, asset, price) rows, then use LAG partitioned by asset to compute returns. After that it's STDDEV_SAMP per asset, a frequency ratio from requests, and min-max normalization across the three assets.
How do I handle the half-away-from-zero rounding rule?+
Don't trust the default ROUND, since some engines round ties to even. Use SIGN(x) * FLOOR(ABS(x) * POWER(10, n) + 0.5) / POWER(10, n) with decimal types. Apply it to each return at 12 places and to the final score at 6 places.
What about the zero-range normalization case?+
If max equals min for volatility or frequency, division by zero breaks the query. Use CASE WHEN max = min THEN 0.5 ELSE (x - min) / (max - min) END for each metric separately. It happens when all three assets share the same value.
Why must it be sample standard deviation?+
The problem says ddof=1, so divide by n-1. Use STDDEV_SAMP, not STDDEV_POP. With only 3 price rows you'd have 2 returns per asset, so the difference changes your answer a lot.
How do I prepare for this in 48 hours?+
Practice LAG, STDDEV_SAMP, CTE chaining, and min-max with window MIN/MAX. Write the unpivot with UNION ALL from memory. Then test your query on a tiny made-up dataset with a zero-range case and an asset with no requests.