Optimizing Slow Queries | Dataford Interview Questions

Describe how you would approach optimizing a slow-running query in a large relational database.

Use the supplied PostgreSQL schema and data to demonstrate an optimized version of the query. Your answer should address diagnosis, execution plans, filtering, joins, aggregation, indexes, statistics, and verification.

Output

One row per customer whose completed order-item value exceeds 1000
Columns: customer_id, customer_name, and total_value
Sort by total_value descending, then customer_id ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Customer identifier
customer_name	VARCHAR(100)	Customer display name
region	VARCHAR(50)	Customer region
is_active	BOOLEAN	Whether the customer is active

orders

Column	Type	Description
order_idPK	INT	Order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Order creation date
status	VARCHAR(20)	Order processing status

order_items

Column	Type	Description
order_item_idPK	INT	Order-item identifier
order_id	INT	Related order identifier
quantity	INT	Number of units
unit_price	DECIMAL(10,2)	Price per unit

Your question is Optimizing Slow Queries. 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