Clean the Tape
Reported by candidates from Millennium's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The Millennium OA reported in September 2026 hands you a messy market-data tape for three assets and asks for a clean one back. Dedupe, sort, treat anything at or below 0 as missing, then forward-fill each asset on its own. It's a SQL question, so you're writing a query, not a loop. If you've never forward-filled in SQL, this feels weird fast. Keep StealthCoder running as a safety net for the live OA in case the window function logic slips away mid-assessment.
The problem
The prices table contains a market-data tape for three assets. Return a cleaned tape with the columns row_index, timestamp, Asset_1, Asset_2, and Asset_3. Cleaning rules Remove rows that are exact duplicates across timestamp and all three asset columns. Sort the remaining rows by timestamp in ascending order. Treat every asset value less than or equal to 0 as missing. For each asset independently, forward-fill a missing value from that asset's most recent positive value in the sorted tape. Never backfill. If an asset has no earlier positive value, keep the output value NULL. Reset the row index after sorting so that row_index is 0, 1, and so on. Tables prices: timestamp (Timestamp), Asset_1 (Decimal), Asset_2 (Decimal), Asset_3 (Decimal) Constraints The input contains at least 1 row and at most 2000 rows. Every timestamp is timezone-neutral and uses whole-second YYYY-MM-DD HH:MM:SS precision. After exact duplicate rows are removed, no two remaining rows share the same timestamp. Each asset value is a decimal or NULL. An exact duplicate may occur more than once and must contribute only one output row. Return rows in ascending timestamp order with the exact result-column order shown.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is a three-step query. First, SELECT DISTINCT on timestamp and all three assets to kill exact duplicates. Second, turn values <= 0 into NULL with CASE WHEN. Third, forward-fill with a window function: for each asset, take the last non-null value up to the current row, ordered by timestamp. In engines without IGNORE NULLS, use a running COUNT of non-null values as a group id, then MAX or FIRST_VALUE over that group. Do this per asset, since each has its own gaps. Finally, ROW_NUMBER() OVER (ORDER BY timestamp) - 1 gives row_index starting at 0. Common pitfall: filling from the raw value instead of the cleaned one, so a 0 or negative leaks through. Another is backfilling by accident. Leading NULLs must stay NULL. If you blank on the group-id trick, StealthCoder is the hedge during the live OA.
StealthCoder is the hedge for the one pattern you didn't drill. It runs invisibly during the screen share.
You can drill Clean the Tape 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. If you're reading this with an OA window open, you're who this was built for.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Millennium's OA.
Millennium reuses patterns across OAs. If you're reading this with an OA window open, you're who this was built for. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Clean the Tape FAQ
What's the trick in the Millennium Clean the Tape question?+
Clean first, fill second. Dedupe with DISTINCT, convert values <= 0 to NULL, then forward-fill each asset separately using a window function over timestamp order. Assign row_index last with ROW_NUMBER minus 1. Most mistakes come from filling before nulling out the bad values.
How do I forward-fill in SQL without IGNORE NULLS?+
Compute a running COUNT of the non-null cleaned values ordered by timestamp. That count becomes a group id. Then take MAX of the cleaned value partitioned by that group id. Rows before the first positive value get a group id of 0 and stay NULL, which matches the no-backfill rule.
Why must each asset be filled independently?+
Each asset has its own gaps and its own last positive value. If Asset_1 is missing but Asset_2 isn't, only Asset_1 gets filled. Use three separate window expressions, one per column, instead of filling a whole row from the previous row.
How do I get row_index to start at 0?+
Use ROW_NUMBER() OVER (ORDER BY timestamp) - 1 after dedupe. Since the constraints say no two remaining rows share a timestamp, the ordering is deterministic. Compute it in the outer query so it numbers the final deduped rows, not the raw ones.
How do I prepare for this in 48 hours?+
Practice window functions: ROW_NUMBER, LAG, running SUM and COUNT with ORDER BY. Write one forward-fill query from scratch with the group-id method. Then test edge cases: leading zeros, repeated duplicates, and a single-row table.