Build Customer Spending Bands from Orders | Dataford Interview Questions

Resolve the question below in Excel. For this SQL version, use the supplied customers and orders data to return a summary for every active customer based on completed orders with non-null amounts.

Output

One row per active customer, including customers without qualifying orders.
Columns: customer_name, region, completed_order_count, total_completed_sales, and spending_band.
Sort by total sales descending, then customer name ascending.

Output details

Assign High when total sales are at least 1000, Medium when at least 500 but below 1000, and Low otherwise.
Represent customers without qualifying orders with completed_order_count 0, total_completed_sales 0, and spending_band Low.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer name
region	VARCHAR(50)	Customer region
status	VARCHAR(20)	Customer account status

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer associated with the order
order_date	DATE	Date the order was placed
amount	DECIMAL(10,2)	Order amount
status	VARCHAR(20)	Order processing status

Your question is Build Customer Spending Bands from Orders. Start with the requirements and the two tables in the Question tab.

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

Problem