Customer Order Count Distribution by Country
Reported by candidates from Apple's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The mistake that sinks a first attempt at this Apple SQL question, reported in October 2026, is joining orders straight to customers and counting rows. You lose the zero-order customers, or you inflate counts after a fan-out. Then the country with no customers vanishes too. It's a conditional aggregation problem wearing a join costume. If you blank during the live OA, StealthCoder runs invisibly as a safety net and shows you the query. But the pattern is short enough to memorize tonight.
The problem
For every country, count its customers in three mutually exclusive order-frequency categories: zero_order_customers: customers who have never placed an order. one_order_customers: customers who have placed exactly one order. multiple_order_customers: customers who have placed at least two orders. Include every country, even when it has no customers. Customers with no matching row in orders must remain in the result. Result Return country_id, country_name, zero_order_customers, one_order_customers, and multiple_order_customers. Order rows by country_name ascending. Tables countries: country_id PK (Integer), country_name (Text) customers: customer_id PK (Integer), country_id (Integer) orders: order_id PK (Integer), customer_id (Integer), country_id (Integer), order_date (Date) Constraints Each customer belongs to exactly one country. Every order belongs to exactly one customer, and its country_id matches that customer's country. Every country_name is unique. A customer's category is based on the number of distinct order rows for that customer. Each input table contains at most 2,000 rows.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is two steps. First, build a per-customer subquery: LEFT JOIN customers to orders on customer_id, GROUP BY customer_id and country_id, and take COUNT(o.order_id) as order_cnt. COUNT on the orders column gives 0 for customers with no match, while COUNT(*) would give 1. That's the classic pitfall. Second, LEFT JOIN countries to that subquery on country_id so empty countries survive. Then use SUM(CASE WHEN order_cnt = 0 THEN 1 ELSE 0 END), and the same for = 1 and >= 2. Wrap in COALESCE or rely on SUM over zero-row groups returning NULL, so COALESCE each to 0. Group by country_id and country_name, then ORDER BY country_name ASC. Don't filter in WHERE after a LEFT JOIN, it silently turns it into an inner join. If you freeze on the live OA, StealthCoder can hand you this structure as a hedge.
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 Customer Order Count Distribution 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. 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.
Customer Order Count Distribution by Country FAQ
How hard is this Apple SQL question really?+
Medium at most. There's no window function or recursion. It tests whether you handle LEFT JOIN, NULLs, and conditional aggregation correctly. Two layers of grouping is the only real complexity, and the 2,000-row cap means performance is irrelevant.
What's the trick to getting zero-order customers right?+
Use COUNT(o.order_id), not COUNT(*), after a LEFT JOIN from customers to orders. COUNT on a column ignores NULLs, so unmatched customers get 0. COUNT(*) would count the NULL-padded row as 1 and put them in the wrong bucket.
Why do I need a subquery instead of one big join?+
Because you need each customer's order count first, then bucket customers. Joining countries, customers, and orders in one go and counting mixes customer-level and order-level rows. Aggregate per customer, then aggregate per country.
How do I keep countries with no customers?+
Start from countries and LEFT JOIN the per-customer result. Countries with no customers get NULL in the counts, so wrap each SUM in COALESCE(..., 0). Otherwise they show NULL instead of 0, which can fail the expected output.
How do I prepare for this in 48 hours?+
Write this query from scratch twice without looking. Then practice variants: count customers with no orders, bucket users by activity, and anti-joins. Focus on LEFT JOIN behavior, COUNT(column) versus COUNT(*), and conditional SUM with CASE.