Apple-style Company ChallengeMediumVerified answerSQLite live

Customer Purchase Frequency Analysis

For each customer who has placed at least one order, show their total orders, total spent, average order value, and rank them by total spend.

  • Window functions
  • Joins
  • Aggregation
  • Numeric functions
  • Sorting

Challenge brief

Understand the request

Customer Success is identifying high-value customers for the AppleCare priority programme and needs a ranked spend analysis.

Calculate customer purchase metrics and rank by total spend using RANK() window function.

Return

  • customer_name (full name)
  • total_orders
  • total_spent (rounded 2)
  • avg_order_value (rounded 2)
  • customer_rank (RANK by total_spent desc)

Constraints

  • Return customers with at least one order
  • Customers with equal spend share a rank
  • Show total spend descending and resolve display ties by customer ID

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)

orders

  • order_idINTEGER
  • customer_idINTEGER
  • total_amountREAL
  • statusVARCHAR(50)

Hints, when you need them

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

Hint 1

Customer names are in customers. Order amounts are in orders. JOIN on customer_id, GROUP BY customer, aggregate spend metrics. RANK() ranks by total spend.

Hint 2

INNER JOIN customers to orders on customer_id. GROUP BY customer_id. SUM(total_amount), COUNT(order_id), AVG(total_amount). RANK() OVER (ORDER BY SUM(total_amount) DESC).

Hint 3

Build question 8 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
SELECT c.first_name || ' ' || c.last_name AS customer_name, COUNT(o.order_id) AS total_orders, ROUND(SUM(o.total_amount), 2) AS total_spent, ROUND(AVG(o.total_amount), 2) AS avg_order_value, RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS customer_rank 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 ORDER BY total_spent DESC, c.customer_id ASC;

Why this works

RANK() runs after GROUP BY so it ranks the aggregated totals. All 6 customers have orders so all appear. Carol White edges out Alice Johnson ($3,798 vs $3,747) despite having fewer orders — she bought more expensive items.

Success check

6 customers — Carol White leads at $3,798, David Brown is last at $399

Expected result

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

customer_nametotal_orderstotal_spentavg_order_valuecustomer_rank
Carol White2379818991
Alice Johnson3374712492
Bob Smith2239811993
Frank Miller1104810484
Emma Davis17997995
David Brown13993996

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.