Optimizing a Poor SQL Query | Dataford Interview Questions

How do you optimize a complex SQL query that is performing poorly?

Using the provided PostgreSQL tables, write an optimized query that returns broker performance for active policies starting in 2025. The result should preserve the required filters, aggregations, and inclusion of brokers without matching claims.

Output

One row per qualifying broker, with broker_id, broker_name, active_policy_count, total_premium, claim_count, and total_claim_amount.
Include only brokers whose 2025 active-policy premium exceeds 1,000.
Sort by total_premium descending, then broker_id ascending.

Schema

brokers

Column	Type	Description
broker_idPK	INT	Unique broker identifier
broker_name	VARCHAR(100)	Broker display name
region	VARCHAR(50)	Broker operating region

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
broker_id	INT	Broker responsible for the policy
status	VARCHAR(20)	Policy lifecycle status
start_date	DATE	Policy start date
premium	NUMERIC(12,2)	Policy premium amount

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
policy_id	INT	Policy associated with the claim
claim_date	DATE	Date the claim was filed
claim_amount	NUMERIC(12,2)	Claim amount

Your question is Optimizing a Poor SQL Query. 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