SQL Joins and Summary Statistics | Dataford Interview Questions

Write a SQL query to join data from different tables and calculate summary statistics.

Use a PostgreSQL query over the supplied policyholder, policy, and claim data. Include only active policies, preserve active policies without paid claims, and report one row per state.

Output

Return state, active_policy_count, avg_annual_premium, max_annual_premium, total_paid_claims, and paid_claim_count.
Treat missing paid claim amounts as zero for totals.
Sort by total_paid_claims descending, then state ascending.

Schema

policyholders

Column	Type	Description
policyholder_idPK	INT	Unique policyholder identifier
policyholder_name	VARCHAR(100)	Policyholder name
state	VARCHAR(2)	Two-letter state code

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
policyholder_id	INT	References the policyholder
policy_number	VARCHAR(20)	Business policy number
status	VARCHAR(20)	Policy lifecycle status
annual_premium	NUMERIC(10,2)	Annual premium amount

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
policy_id	INT	References the policy
claim_status	VARCHAR(20)	Claim processing status
paid_amount	NUMERIC(10,2)	Amount paid for the claim

Your question is SQL Joins and Summary Statistics. 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