Classify Customer Membership by Total Spend | Dataford Interview Questions

Your question is Classify Customer Membership by Total Spend. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Scenario

A retail company wants to analyze customer purchase behavior and assign membership status based on total spending.

Write a SQL query to produce a summary of each customer's total spending, order count, and membership status.

Join customers and orders to aggregate spending and order count per customer.
Assign membership status:
- 'Gold' if total spending is at least 900
- 'Silver' if total spending is at least 500 but less than 900
- 'Bronze' otherwise
Include all customers, even those without orders (show 0 and 'Bronze' for them).
Order results by total spending descending.

customers

Column	Type	Description
customer_idPK	INT	Unique identifier for each customer
customer_name	VARCHAR(255)	Name of the customer

orders

Column	Type	Description
order_idPK	INT	Unique identifier for each order
customer_id	INT	Reference to the customer who placed the order
amount	DECIMAL(10,2)	Total amount for the order