Transaction Balance Over Threshold
Reported by candidates from IMC's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The structure this IMC question hinges on is a grouped aggregate: one sum per account, filtered after the grouping. IMC reported it in September 2020, and it's a short SQL task that punishes one small slip. You get LOGIN and TRANSACT, and you return usernames whose net balance is strictly above 10000. If the invite's in your inbox, the whole job is GROUP BY, SUM, and HAVING. If you freeze on the OA, StealthCoder is the invisible safety net that reads the problem and hands you the query.
The problem
You are given two tables, LOGIN and TRANSACT. LOGIN ACCOUNT CHAR(16) USERNAME CHAR(20) FIRST_NAME CHAR(20) LAST_NAME CHAR(30) TRANSACT ACCOUNT CHAR(16) AMOUNT INT TIMESTAMP DATE TRANSACT contains every change made to an account. Credits are positive, debits are negative, and every account starts with a balance of 0. Return exactly one column, USERNAME, containing every username whose account has a net balance strictly greater than 10000. Compute an account's net balance as the sum of all of its TRANSACT.AMOUNT values. The output row order is not significant. Tables LOGIN: ACCOUNT (Text), USERNAME (Text), FIRST_NAME (Text), LAST_NAME (Text) TRANSACT: ACCOUNT (Text), AMOUNT (Integer), TIMESTAMP (Date) Constraints For this practice version, every input cell is non-null. LOGIN.ACCOUNT identifies at most one login row. An account may have any number of TRANSACT rows. Text values respect the source-declared CHAR lengths. AMOUNT is a signed integer: positive values are credits and negative values are debits. TIMESTAMP is represented as an ISO date in YYYY-MM-DD form and does not affect the result. Either table may be empty. An account with no transactions has balance 0, and transactions without a matching LOGIN row contribute no username.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is to aggregate TRANSACT by ACCOUNT first, then keep groups where SUM(AMOUNT) > 10000. That filter belongs in HAVING, not WHERE, because WHERE runs before the sum exists. Join to LOGIN on ACCOUNT to pull the username, and select only USERNAME. Credits are positive and debits negative, so a plain SUM already gives the net balance. The pitfall is the strict comparison. A balance of exactly 10000 must not appear, so use > and not >=. Another slip is selecting extra columns. The output is exactly one column. Transactions with no matching LOGIN row drop out naturally with an inner join. Accounts with no transactions have balance 0, so they never pass the threshold and you don't need a LEFT JOIN. TIMESTAMP is irrelevant, so ignore it. If you blank during the live OA, StealthCoder is there as the hedge, but this query is only about five lines.
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 Transaction Balance Over Threshold 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 IMC's OA.
IMC 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.
Transaction Balance Over Threshold FAQ
How hard is the IMC Transaction Balance Over Threshold question really?+
It's easy if you know GROUP BY with HAVING. It's a single join plus one aggregate. The only real risk is a sloppy mistake, like using WHERE on the sum or returning extra columns. Write it once from memory and you're set.
What's the trick to this query?+
Sum AMOUNT per account, then filter the groups with HAVING SUM(AMOUNT) > 10000. Join LOGIN on ACCOUNT to get the username. Select only USERNAME. Signed amounts mean no CASE logic is needed for credits versus debits.
Should I use WHERE or HAVING for the 10000 filter?+
HAVING. WHERE filters individual rows before grouping, so it can't see the summed balance. HAVING filters after the aggregate is computed. Using WHERE on SUM throws an error in most SQL engines.
Do I need a LEFT JOIN for accounts with no transactions?+
No. Those accounts have a balance of 0, which isn't above 10000, so they shouldn't appear. An inner join between LOGIN and the grouped transactions is correct and simpler. Transactions without a LOGIN match also drop out.
How do I prepare for this in 48 hours?+
Write three or four aggregate-and-join queries from scratch: a SUM with HAVING, a COUNT with HAVING, and a join to a lookup table. Check strict versus inclusive comparisons each time. That covers this IMC pattern reported in September 2020.