Nested SQL Query Writing | Dataford Interview Questions

Tell me about how you would write a nested SQL query to answer a business question.

Using the supplied customers and orders tables, demonstrate this by identifying customers whose completed order total exceeds the average completed order total across qualifying customers.

Output

One row per qualifying customer with customer_id, customer_name, and total_completed_amount.
Exclude customers without completed orders and non-completed orders from the calculation.
Order by total completed amount descending, then customer_id 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 associated with the order
status	VARCHAR(20)	Current order status
amount	NUMERIC(12,2)	Order amount

Your question is Nested SQL Query Writing. 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