SQL Optimization and Subqueries | Dataford Interview Questions

What is optimization, and what is a subquery in SQL?

Use the supplied PostgreSQL tables to demonstrate the subquery concept by finding customers whose settled transaction total exceeds the average settled total across transaction groups. Briefly explain how the query could be optimized.

Output

One row per matching customer with customer_id, customer_name, and total_settled_amount.
Include only totals greater than the average customer total, ordered by total descending and customer_id ascending.

Schema

customers

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

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
amount	NUMERIC(12,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status

Your question is SQL Optimization and Subqueries. 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