Explain and Optimize a JOIN | Dataford Interview Questions

Walk me through this SQL query on my screen: what is this specific JOIN accomplishing, and how would you optimize it?

Use the supplied PostgreSQL schema and data to explain the query and provide an optimized version. Preserve policies that have no matching claims.

Output

One row per active policy with policy_id, policy_type, claim_count, and total_paid
Include active policies without claims, showing zero counts and totals
Include claims from 2025 only and order by policy_id ascending

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
policy_type	VARCHAR(40)	Insurance policy classification
is_active	BOOLEAN	Whether the policy is currently active

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 recorded
paid_amount	NUMERIC(12,2)	Amount paid for the claim
claim_status	VARCHAR(20)	Current claim status

Your question is Explain and Optimize a JOIN. 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.