Aggregate a Large Dataset With GROUP BY
Reported by candidates from Alpaca's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The detail that trips people on this Alpaca OA, reported in September 2022, is the second example: two values of 1000000000 for the same key add up to 2000000000, which already overflows a signed 32-bit int. It's a GROUP BY written as a function. Sum values per key, then print key=sum in sorted key order. It's a hash-table problem with a small trap built in. If you blank on the accumulation type or the output format, StealthCoder is the safety net that runs invisibly during the live assessment and gets you unstuck.
The problem
Implement the core of SELECT key, SUM(value)... GROUP BY key. The arrays describe rows in a large dataset: row i has grouping key keys[i] and integer measure values[i]. Return one string key=sum for every distinct key, sorted lexicographically by key. Use wide integer accumulation so group totals do not overflow a 32-bit sum. Function groupBySum(keys: String[], values: int[]) → String[] Examples Example 1 keys = ["west","east","west","north"] values = [5,7,-2,4] return = ["east=7","north=4","west=3"] Rows are accumulated by key and formatted in key order. Example 2 keys = ["a","a","b"] values = [1000000000,1000000000,-3] return = ["a=2000000000","b=-3"] The same group can contain large and negative values. Constraints 0 <= keys.length = values.length <= 200000 Keys are nonempty strings of length at most 100. -10^9 <= values[i] <= 10^9
Reported by candidates. Source: FastPrep
Pattern and pitfall
The pattern is a hash map from key to a running total. One pass over the arrays, add values[i] to the entry for keys[i], and you're done with the aggregation. Then pull out the keys, sort them lexicographically, and format each as key=sum. The pitfall is the sum type. With up to 200000 rows and values up to 10^9 in magnitude, totals can reach 2x10^14, so use a 64-bit integer (long in Java, long long in C++). Python handles it natively. The second pitfall is sorting by the wrong thing. Sort by key string, not by sum, and don't rely on the map's insertion order. Handle the empty input by returning an empty array. Negative sums print with a minus sign, like b=-3. Complexity is O(n + k log k) where k is the number of distinct keys. If you freeze on the 64-bit detail during the live OA, StealthCoder is the hedge that surfaces it.
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 Aggregate a Large Dataset With GROUP BY 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 Alpaca's OA.
Alpaca 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.
Aggregate a Large Dataset With GROUP BY FAQ
What's the trick in the Alpaca group-by-sum problem?+
Accumulate in a 64-bit integer. Individual values fit in 32 bits, but group totals across 200000 rows can reach about 2x10^14. Use a hash map from key to long, then sort the keys and format the output. The rest is routine.
How hard is this OA question really?+
Easy. It's a single pass with a hash map plus a sort. The only real way to lose points is a 32-bit overflow or sorting by the wrong field. If you've written a word-count style counter before, you've already done the hard part.
Do I sort by key or by sum?+
By key, lexicographically. Example 1 returns east, north, west, which is alphabetical, not ordered by total. Sort the distinct keys as plain strings, then build each key=sum string in that order.
What edge cases should I test before submitting?+
Test empty arrays, which should return an empty list. Test a single key repeated many times, negative totals, and a total that hits exactly 2000000000 like Example 2. Also test keys that share prefixes, such as a and ab, to confirm the sort order.
How do I prepare for this in 48 hours?+
Write the hash map aggregation once in your language of choice. Check how it handles 64-bit sums and how to sort string keys. Practice formatting output strings. This is a 15 minute exercise, so spend the rest of your time on harder problem types.