Classify Customer Membership by Total Spend

Scenario

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

Task

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

Requirements

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.

Table Definitions

customers

customer_id	INT	Primary key	Customer unique ID
customer_name	VARCHAR(255)		Customer name

orders

order_id	INT	Primary key	Order unique ID
customer_id	INT	Foreign key	Customer placing the order
amount	DECIMAL(10,2)		Order amount

Sample Data

customers

customer_id	customer_name
1	Alice
2	Bob
3	Charlie
4	Dana
5	Eve

orders

order_id	customer_id	amount
1	1	150
2	1	300
3	2	200
4	3	50
5	2	600
6	1	450
7	5	0
8	1	NULL
9	4	NULL

Expected Output

customer_name	total_spent	order_count	membership_status
Alice	900.00	3	Gold
Bob	800.00	2	Silver
Charlie	50.00	1	Bronze
Eve	0.00	1	Bronze
Dana	0.00	0	Bronze

Scenario

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

Task

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

Requirements

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.

Table Definitions

customers

customer_id	INT	Primary key	Customer unique ID
customer_name	VARCHAR(255)		Customer name

orders

order_id	INT	Primary key	Order unique ID
customer_id	INT	Foreign key	Customer placing the order
amount	DECIMAL(10,2)		Order amount

Sample Data

customers

customer_id	customer_name
1	Alice
2	Bob
3	Charlie
4	Dana
5	Eve

orders

order_id	customer_id	amount
1	1	150
2	1	300
3	2	200
4	3	50
5	2	600
6	1	450
7	5	0
8	1	NULL
9	4	NULL

Expected Output

customer_name	total_spent	order_count	membership_status
Alice	900.00	3	Gold
Bob	800.00	2	Silver
Charlie	50.00	1	Bronze
Eve	0.00	1	Bronze
Dana	0.00	0	Bronze

Scenario

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

Task

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

Requirements

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.

Table Definitions

customers

customer_id	INT	Primary key	Customer unique ID
customer_name	VARCHAR(255)		Customer name

orders

order_id	INT	Primary key	Order unique ID
customer_id	INT	Foreign key	Customer placing the order
amount	DECIMAL(10,2)		Order amount

Sample Data

customers

customer_id	customer_name
1	Alice
2	Bob
3	Charlie
4	Dana
5	Eve

orders

order_id	customer_id	amount
1	1	150
2	1	300
3	2	200
4	3	50
5	2	600
6	1	450
7	5	0
8	1	NULL
9	4	NULL

Expected Output

customer_name	total_spent	order_count	membership_status
Alice	900.00	3	Gold
Bob	800.00	2	Silver
Charlie	50.00	1	Bronze
Eve	0.00	1	Bronze
Dana	0.00	0	Bronze

Scenario

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

Task

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

Requirements

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.

Table Definitions

customers

customer_id	INT	Primary key	Customer unique ID
customer_name	VARCHAR(255)		Customer name

orders

order_id	INT	Primary key	Order unique ID
customer_id	INT	Foreign key	Customer placing the order
amount	DECIMAL(10,2)		Order amount

Sample Data

customers

customer_id	customer_name
1	Alice
2	Bob
3	Charlie
4	Dana
5	Eve

orders

order_id	customer_id	amount
1	1	150
2	1	300
3	2	200
4	3	50
5	2	600
6	1	450
7	5	0
8	1	NULL
9	4	NULL

Expected Output

customer_name	total_spent	order_count	membership_status
Alice	900.00	3	Gold
Bob	800.00	2	Silver
Charlie	50.00	1	Bronze
Eve	0.00	1	Bronze
Dana	0.00	0	Bronze

Interview Guides

Scenario

Task

Requirements

Table Definitions

Sample Data

Expected Output

Classify Customer Membership by Total Spend

Scenario

Task

Requirements

Table Definitions

Sample Data

Expected Output

Practice SQL

Classify Customer Membership by Total Spend

Scenario

Task

Requirements

Table Definitions

Sample Data

Expected Output

Classify Customer Membership by Total Spend

Scenario

Task

Requirements

Table Definitions

Sample Data

Expected Output

Practice SQL