Optimize Database Queries | Dataford Interview Questions

How would you optimize database queries using indices or denormalization?

Write a PostgreSQL query using the supplied customers and orders tables, then explain which indexes or denormalized structures could improve it.

Output

One row per customer.
Return customer_id, customer_name, completed_order_count, and total_completed_amount.
Include all customers, considering only completed orders placed in January 2025. Sort by total amount descending, order count descending, then customer ID ascending.

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
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status
total_amount	NUMERIC(12,2)	Total monetary value of the order

Your question is Optimize Database Queries. 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.