Summarize Customer Records
Reported by candidates from Agoda's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The Agoda OA reported in October 2023 looks like a reporting task, but the whole thing hinges on one data structure: a result set stacked from several grouped queries with UNION ALL. Total customers, per-city counts, per-country counts, top country by contracts, and unique cities all land in one table with columns metric, key, and value. If you see that early, it's mostly typing. If you don't, you'll write five queries and stall on how to glue them. StealthCoder sits invisibly on your screen as a safety net if you blank mid-assessment, but the shape is simple once you see it.
The problem
Analyze a customer dataset with company, city, country, employee-count, contract-count, and contract-cost fields. Return a normalized summary containing: the total customer count; customer counts for every city in ascending city order; customer counts for every country in ascending country order; the country with the largest sum of signed contracts and that sum; and the number of unique cities. If several countries tie for the largest contract count, select the alphabetically larger country using case-sensitive comparison. Tables customers: ID (Text), NAME (Text), CITY (Text), COUNTRY (Text), CPERSON (Text), EMPLCNT (Integer), CONTRCNT (Integer), CONTRCOST (Decimal) Constraints All input columns follow the source schema and are non-null. The source's formatted multi-section output is represented as ordered rows with columns metric, key, and value.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is building each section as its own SELECT with the same three columns, then combining with UNION ALL and ordering by a section rank, then key. Cast counts to the same type so the union doesn't break. Total is COUNT(*) with a NULL or empty key. City and country sections are GROUP BY with COUNT(*), sorted ascending. Unique cities is COUNT(DISTINCT CITY). The top country is the sneaky part: SUM(CONTRCNT) per country, ORDER BY the sum DESC, then COUNTRY DESC for the tie rule, LIMIT 1. Descending alphabetical gives you the alphabetically larger country. The pitfall is sorting ascending out of habit, or forgetting that case-sensitive comparison depends on collation. Also watch the ORDER BY after a UNION, since it applies to the whole result. If the ordering or union typing trips you up live, StealthCoder is the hedge that gets you a working query fast.
If this hits your live OA and you blank, StealthCoder solves it in seconds, invisible to the proctor.
You can drill Summarize Customer Records 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 would have shipped this the night before his JPMorgan OA if he'd had it.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Agoda's OA.
Agoda reuses patterns across OAs. Built by an Amazon engineer who would have shipped this the night before his JPMorgan OA if he'd had it. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Summarize Customer Records FAQ
How hard is the Agoda Summarize Customer Records question really?+
Easy to medium. No window functions are required. The difficulty is assembling five different aggregations into one ordered output with metric, key, and value columns. If you've written a UNION ALL before, you're fine.
What's the trick to the output format?+
Give every section the same three columns and add a hidden sort rank. UNION ALL the sections, then ORDER BY rank and key. Without the rank, the sections interleave and the ordering breaks.
How do I handle the country tie rule?+
Sum CONTRCNT by country, then order by that sum descending and COUNTRY descending, and take one row. Descending name order picks the alphabetically larger country. Watch collation, since the problem says case-sensitive.
Do I need to worry about NULLs?+
Not much. The problem says all input columns are non-null. You'll still need placeholder values for the key column in the total and unique-city rows so the union lines up. Cast types consistently.
How do I prepare in 48 hours?+
Practice GROUP BY with COUNT and SUM, COUNT(DISTINCT), and UNION ALL with a sort rank. Write the full five-section query once from memory against a small test table. Check ordering and tie handling carefully.