iPhone Model Sales Share by Country
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 looks like a share-of-total problem, but it hinges on one structure: a self-join on products, where each model links to its previous_model_id. If you've got an invite for this, the whole thing is aggregate units per country and product, then compare each model to its predecessor. Miss the zero-unit side and your row count is wrong. If you blank on the join shape during the live assessment, StealthCoder sits invisibly on your screen as a safety net and hands you the query. Know the shape first, though. It's three CTEs and a join.
The problem
Apple tracks iPhone orders by country. For every country and every model that names a previous-generation model, compare the two models' shares of that country's total iPhone units sold. Return one row when either the current model or its previous model sold at least one unit in the country. A missing side of the comparison has zero units. Result Return country_name, product_name, current_share_percent, previous_share_percent, and share_difference_percentage_points. A model share is its units divided by all units sold in the same country, multiplied by 100 and rounded to two decimal places. The difference is current share minus previous share. Order rows by country_name ascending, then product_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 row in products represents one iPhone model, and product_name values are unique. Every previous_model_id is either NULL or identifies an older product. Every country_name is unique. Each input table contains at most 2,000 rows.
Reported by candidates. Source: FastPrep
Pattern and pitfall
Build it in layers. First, join orders, order_items and countries, then sum quantity by country and product. Second, compute the country total, either with a window function SUM(units) OVER (PARTITION BY country_id) or a separate grouped CTE. Third, self-join products to itself on previous_model_id, so each current model pairs with its predecessor. The trap is the inclusion rule: return a row when either side sold at least one unit. An inner join drops rows where one side sold nothing. Build a candidate set of country and current-model pairs from both sides (a UNION of current and previous sales), then LEFT JOIN the unit counts and wrap them in COALESCE(...,0). Compute shares as ROUND(100.0 * units / total, 2). Use 100.0 to avoid integer division. Difference is current minus previous, computed from the rounded or raw values consistently. Order by country_name, then product_name. If you freeze on the union step in the live OA, StealthCoder is the hedge.
If this hits your live OA and you blank, StealthCoder solves it in seconds, invisible to the proctor.
You can drill iPhone Model Sales Share by Country 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 Apple's OA.
Apple 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.
iPhone Model Sales Share by Country FAQ
What's the trick in the Apple iPhone share problem?+
The trick is the self-join on previous_model_id plus keeping rows where only one side sold. Aggregate units per country and product first, then pair each model with its predecessor. Use COALESCE to turn the missing side into zero units instead of losing the row.
Why does my result have too few rows?+
You probably used an inner join. The problem says return a row if either the current or previous model sold at least one unit. So build the country and model pairs from both sides, then LEFT JOIN the unit totals. Inner joins silently drop one-sided cases.
How do I get the country total for the share?+
Sum quantity across all products in a country, not just the two being compared. Use SUM(units) OVER (PARTITION BY country_id) on the aggregated data, or a separate grouped CTE joined back. The denominator is the whole country's iPhone units.
How do I avoid rounding and division bugs?+
Multiply by 100.0, not 100, so the division isn't integer-based. Then wrap in ROUND(..., 2). Compute the difference the same way every time, ideally from the unrounded shares then rounded, and check your output against a hand example.
How do I prepare for this in 48 hours?+
Practice three things: grouped aggregation across a multi-table join, window functions with PARTITION BY, and self-joins with LEFT JOIN plus COALESCE. Write this exact query once from scratch with a tiny dataset. That covers nearly everything the problem tests.