Apple-style Company ChallengeHardVerified answerSQLite live

Top-Spending Customer per Country

In each country, which customer has spent the most on Apple products, and how much have they spent?

  • CTEs
  • Window functions
  • Joins
  • Subqueries
  • Aggregation

Challenge brief

Understand the request

International Sales is launching a VIP rewards programme and needs to identify the top-spending Apple customer in each country.

Find the highest-spending customer per country using a CTE and ROW_NUMBER() window function.

Return

  • country
  • customer_name (full name)
  • total_orders
  • total_spent (rounded 2)
  • last_order_date

Constraints

  • Return one top-spending customer per country with orders
  • When spend ties, the lower customer ID wins
  • Show the largest country-leading spend first and resolve ties by country

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

customers

  • customer_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • countryVARCHAR(50)

orders

  • order_idINTEGER
  • customer_idINTEGER
  • order_dateDATETIME
  • total_amountREAL

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Two-step pattern: CTE 1 aggregates total spend per customer. CTE 2 uses ROW_NUMBER() OVER (PARTITION BY country ORDER BY total_spent DESC) to rank customers within each country. Outer query: WHERE rn = 1.

Hint 2

CTE customer_stats: JOIN customers to orders, GROUP BY customer, compute total_spent. CTE ranked: add ROW_NUMBER() OVER (PARTITION BY country ORDER BY total_spent DESC). Outer: WHERE rn = 1.

Hint 3

Build question 19 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH customer_stats AS (SELECT c.customer_id, c.first_name || ' ' || c.last_name AS customer_name, c.country, COUNT(o.order_id) AS total_orders, ROUND(SUM(o.total_amount), 2) AS total_spent, MAX(o.order_date) AS last_order_date FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.first_name, c.last_name, c.country), ranked AS (SELECT customer_id, customer_name, country, total_orders, total_spent, last_order_date, ROW_NUMBER() OVER (PARTITION BY country ORDER BY total_spent DESC, customer_id ASC) AS rn FROM customer_stats) SELECT country, customer_name, total_orders, total_spent, last_order_date FROM ranked WHERE rn = 1 ORDER BY total_spent DESC, country

Why this works

ROW_NUMBER() assigns 1 to the top spender per country. WHERE rn = 1 extracts exactly one row per country. David Brown (USA, $399) is excluded because Alice Johnson outspends him in USA.

Success check

3 countries — Canada: Carol White ($3,798), USA: Alice Johnson ($3,747), UK: Emma Davis ($799)

Expected result

Use this output to verify values, aliases, ordering, and row count.

countrycustomer_nametotal_orderstotal_spentlast_order_date
CanadaCarol White237982024-02-10 14:30:00
USAAlice Johnson337472024-02-08 12:00:00
UKEmma Davis17992024-02-03 10:20:00

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.