Amazon-style Company ChallengeMediumVerified answerSQLite live

Prime vs Non-Prime Order Value Comparison

Return one Prime row and one Non-Prime row with customer_count, total_orders, avg_order_value, and total_revenue.

  • Joins
  • Aggregation
  • CASE expressions
  • Numeric functions
  • Sorting

Challenge brief

Understand the request

Prime Business Team needs to quantify the revenue impact of Prime membership to justify the subscription model in the next board presentation.

Compare order volume and revenue between Prime and Non-Prime customers.

Return

  • membership_type (Prime or Non-Prime)
  • customer_count
  • total_orders
  • avg_order_value (rounded to 2 decimals)
  • total_revenue (rounded to 2 decimals)

Constraints

  • Label membership groups as Prime and Non-Prime
  • Count customers even when they have no orders
  • Order the two groups with Prime first

Data you will use

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

customers

  • customer_idINTEGER
  • prime_memberINTEGER

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

The membership flag (prime_member) is in customers. Order amounts are in orders. Use a CASE WHEN expression to label each customer as Prime or Non-Prime. Use LEFT JOIN so all customers appear even if they have no orders.

Hint 2

LEFT JOIN customers to orders on customer_id. GROUP BY c.prime_member. In SELECT: CASE WHEN c.prime_member = 1 THEN 'Prime' ELSE 'Non-Prime' END AS membership_type. COUNT(DISTINCT c.customer_id) for customer count, COUNT(o.order_id) for orders, AVG and SUM for value metrics.

Hint 3

Scaffold: preserve customers while joining orders, label the membership flag, and group once to calculate distinct customers, orders, average value, and revenue.

Verified SQL answer

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

Reveal solution and explanation
SELECT CASE WHEN c.prime_member = 1 THEN 'Prime' ELSE 'Non-Prime' END AS membership_type, COUNT(DISTINCT c.customer_id) AS customer_count, COUNT(o.order_id) AS total_orders, ROUND(AVG(o.total_amount), 2) AS avg_order_value, ROUND(SUM(o.total_amount), 2) AS total_revenue FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.prime_member ORDER BY membership_type DESC;

Why this works

GROUP BY c.prime_member (0 or 1) creates two groups. CASE WHEN converts the flag to a readable label. LEFT JOIN ensures customers without orders still count in customer_count. The result quantifies the Prime lift: Prime customers generate 5x more revenue per customer.

Success check

2 rows — Prime (4 customers, 8 orders, 8.48 avg, 87.87 total) vs Non-Prime (2 customers, 2 orders, 2.48 avg, 44.97 total)

Expected result

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

membership_typecustomer_counttotal_ordersavg_order_valuetotal_revenue
Prime4898.48787.87
Non-Prime2272.48144.97

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.