First vs Returning Customer Revenue

SQLMediumMid levelAmazon~15 min

Problem

**[Asked at Amazon]**

Amazon's finance team splits revenue into two buckets per customer:
- The **first purchase** ever made by a customer
- All **subsequent purchases** (repeat buys)

Calculate total revenue for each bucket.
Return `purchase_type` ('first_purchase' or 'repeat_purchase') and `total_revenue`,
ordered by purchase_type.

Tables

Example rows — the live problem includes the full dataset.

orders

order_id	customer_id	product	amount	order_date

Expected output

Your answer should return 2 rows with the columns purchase_type, total_revenue.

Starter code (SQL)

SELECT *
FROM orders;

Solve this SQL question free

Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.

Solve it now → Create free account

Solution & explanation

Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.