Top Five Countries by Product Revenue
Reported by candidates from Apple's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The Apple OA reported in October 2026 hands you a top-N-per-group SQL problem: the five countries with the highest revenue for each product. The detail that trips people is the tiebreaker. Equal sales_amount means alphabetical country_name decides the rank. It's a join, a group-by, and a window function, nothing exotic. But one sloppy ORDER BY inside the window costs you the whole test case. If you blank on the syntax, StealthCoder runs invisibly during the live OA as a safety net, so one lost minute doesn't sink you.
The problem
For each Apple product, find the five countries with the highest total sales revenue. A line item contributes quantity * unit_price to its order country. Sum all line-item revenue for the same product and country. Result Return product_id, product_name, country_id, country_name, and sales_amount. Rank countries independently for each product by sales_amount descending. When amounts are equal, rank the country whose English country_name is alphabetically smaller first. Return at most five rows per product. Order the final result by product_id ascending, then sales_amount descending, then country_name ascending. Tables products: product_id PK (Integer), product_name (Text), previous_model_id (Integer) countries: country_id PK (Integer), country_name (Text) orders: order_id PK (Integer), customer_id (Integer), country_id (Integer), order_date (Date) order_items: order_id PK (Integer), product_id PK (Integer), quantity (Integer), unit_price (Decimal) Constraints Every quantity is a positive integer. Every unit_price is a non-negative exact decimal. Every product_name and country_name is unique. Only product-country pairs with at least one line item appear in the result. Each input table contains at most 2,000 rows.
Reported by candidates. Source: FastPrep
Pattern and pitfall
Build it in two layers. First, join order_items to orders on order_id, then to products and countries. Group by product_id, product_name, country_id, country_name and compute SUM(quantity * unit_price) as sales_amount. Second, wrap that in a CTE or subquery and apply ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sales_amount DESC, country_name ASC). Keep rows where the rank is 5 or less. The pitfall is RANK or DENSE_RANK. Ties could then return more than five rows, which breaks the at most five rule. ROW_NUMBER with the name tiebreaker is deterministic. Finish with ORDER BY product_id, sales_amount DESC, country_name. Don't join products twice, and don't forget order_items has a composite key. StealthCoder is your hedge if the window clause slips your mind mid-assessment.
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 Top Five Countries by Product Revenue 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 Apple's OA.
Apple 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.
Top Five Countries by Product Revenue FAQ
What's the trick in the Apple top five countries per product question?+
It's top-N per group. Aggregate revenue per product and country first, then rank with ROW_NUMBER partitioned by product_id. Order the window by sales_amount descending, then country_name ascending. Filter to rank 5 or less in an outer query, since you can't filter on a window function in WHERE.
Should I use RANK, DENSE_RANK, or ROW_NUMBER?+
Use ROW_NUMBER. The problem says return at most five rows per product and defines a full tiebreaker by country_name. Names are unique, so ROW_NUMBER gives a strict order. RANK would let ties pass the cutoff and return extra rows.
How do I compute revenue correctly?+
Multiply quantity by unit_price on each order_items row, then SUM per product and country. The country comes from orders, not order_items, so you must join order_items to orders on order_id before grouping. Then join countries for the name.
Do I need to handle NULLs or products with no sales?+
Not really. The constraints say only product-country pairs with at least one line item appear, so inner joins are right. Quantity is positive and unit_price is non-negative, so there are no NULL math surprises. A LEFT JOIN would add rows you don't want.
How do I prepare for this in 48 hours?+
Write the query from scratch three times on a small made-up dataset. Focus on the CTE plus ROW_NUMBER pattern and the final three-column ORDER BY. Check that your output column names match the spec exactly. That covers most of what this question tests.