Amazon-style Company ChallengeEasyVerified answerSQLite live

Customer Purchase Frequency

Which customers have placed more than one order, and what is their total spend and order count?

  • Joins
  • Aggregation
  • HAVING
  • Numeric functions
  • Sorting

Challenge brief

Understand the request

Customer Retention Team wants to identify loyal repeat customers and understand their total spend.

Find customers with more than one order, showing total orders and total spend.

Return

  • customer_name (full name)
  • email
  • total_orders
  • total_spent (rounded to 2 decimals)

Constraints

  • Only customers with more than 1 order
  • Order by total_spent descending

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)
  • emailVARCHAR(100)

orders

  • order_idINTEGER
  • customer_idINTEGER
  • total_amountREAL

Hints, when you need them

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

Hint 1

Customer names are in customers. Orders are in orders. Join them on customer_id, then group by customer to count orders and sum spend. Use HAVING to keep only customers with more than 1 order.

Hint 2

INNER JOIN customers to orders on customer_id. GROUP BY customer_id (and name/email for SELECT validity). Use COUNT(o.order_id) AS total_orders and ROUND(SUM(o.total_amount), 2) AS total_spent. Add HAVING COUNT(o.order_id) > 1.

Hint 3

Scaffold: join customers to orders, group at customer grain, calculate count and spend, retain repeat purchasers, then sort by spend.

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, c.email, COUNT(o.order_id) AS total_orders, ROUND(SUM(o.total_amount), 2) AS total_spent 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.email HAVING COUNT(o.order_id) > 1 ORDER BY total_spent DESC;

Why this works

HAVING COUNT > 1 filters groups after aggregation — you cannot use WHERE here because the count is computed after grouping. GROUP BY must include all non-aggregated SELECT columns (customer_id, first_name, last_name, email) to satisfy SQL rules.

Success check

2 customers — Bob Smith (74.95, 3 orders) and Alice Johnson (92.95, 3 orders)

Expected result

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

customer_nameemailtotal_orderstotal_spent
Bob Smithbob.s@email.com3374.95
Alice Johnsonalice.j@email.com3292.95

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.