Join and Aggregate Multiple Tables | Dataford Interview Questions

Write an SQL query to join multiple tables and aggregate the results.

Use the provided customer, order, and order item data. Include customers without completed orders or order items, and consider only completed orders when calculating totals.

Output

One row per customer with customer_name, completed_orders, total_units, and total_revenue.
Include customers with zero completed orders, using zero totals.
Order by total_revenue descending, then customer_name ascending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
status	VARCHAR(20)	Current order status

order_items

Column	Type	Description
order_item_idPK	INT	Unique order item identifier
order_id	INT	Order containing the item
quantity	INT	Number of units in the item row
unit_price	DECIMAL(10,2)	Price per unit

Your question is Join and Aggregate Multiple Tables. Start with the requirements and the three 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