Block Transaction Count Trends
Reported by candidates from Chainalysis's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The edge case that kills this Chainalysis query is the first row, and it's the one candidates skip. Chainalysis reported this SQL question in March 2025: for each Bitcoin block, pull the previous block's transaction count and label the trend Increased, Decreased, or Same. It's a LAG problem with a NULL trap and a gap trap. Heights aren't guaranteed consecutive, so a self-join on height minus 1 quietly drops rows. If you blank on the window syntax during the live OA, StealthCoder runs invisibly as a safety net and hands you the query.
The problem
The btc_blocks table records one Bitcoin block per row with its height and transaction count. For every block, show its block_height, current transaction_count, and the preceding block row's transaction count as previous_transaction_count. Add a trend label: Increased, Decreased, or Same by comparing the current count with the previous count. Order rows by block_height ascending. For the earliest block, return NULL as the previous count and Same as the trend. Tables btc_blocks: block_height PK (Integer), transaction_count (Integer) Constraints btc_blocks contains from 1 through 2,000 rows. block_height is a unique nonnegative integer. transaction_count is a nonnegative integer. The previous block means the preceding available row after sorting by block_height, even when heights are not consecutive.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is LAG(transaction_count) OVER (ORDER BY block_height). It returns the preceding available row after sorting, which is exactly what the problem asks for, even when heights skip. A self-join on block_height - 1 is the common wrong answer. It fails on gaps and needs extra work for the first row. The second pitfall is the label. Comparing against a NULL previous count gives NULL in a CASE, so a naive CASE falls through to the wrong branch. Handle it explicitly: WHEN previous IS NULL OR current = previous THEN 'Same', WHEN current > previous THEN 'Increased', ELSE 'Decreased'. Wrap LAG in a subquery or CTE so you don't repeat it. Order the final output by block_height ascending. If the window syntax slips under pressure, StealthCoder is the hedge that gives you the clean version on screen without the proctor seeing it.
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 Block Transaction Count Trends 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 Chainalysis's OA.
Chainalysis 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.
Block Transaction Count Trends FAQ
What's the trick in the Chainalysis Block Transaction Count Trends question?+
Use LAG ordered by block_height to get the previous row's transaction_count. Then a CASE expression builds the trend label. The part people miss is that the first block has no previous row, so it must return NULL for the count and Same for the trend.
Why not self-join on block_height - 1?+
The problem says the previous block is the preceding available row, even when heights aren't consecutive. A join on height minus 1 misses any block after a gap and returns NULL where a real previous row exists. LAG handles gaps automatically because it works on sorted row order.
How do I handle the NULL for the earliest block?+
LAG returns NULL by default when no prior row exists, which is what you want for previous_transaction_count. In the CASE, check previous IS NULL first and return Same. If you compare first, NULL makes both comparisons unknown and you'd land in the ELSE branch, giving Decreased.
How hard is this SQL question really?+
It's easy if you know window functions and a trap if you don't. There's one LAG, one CASE, and one ORDER BY. The difficulty is in the edge cases: the NULL first row and non-consecutive heights. Write it in a CTE and test those two cases mentally.
How do I prepare for this in 48 hours?+
Practice LAG and LEAD with ORDER BY, then CASE logic that handles NULL before comparisons. Write three or four row-over-row comparison queries from scratch. Remember to order the final result set explicitly, since window ordering doesn't guarantee output order.