Join Policies and Claims in SQL | Dataford Interview Questions

Write a SQL query to join two tables containing policyholder information and claims data, filtering for specific date ranges and calculating the average claim amount per region. Include policyholders whose policy_start_date is on or before 2024-06-30, and claims dated from 2024-01-01 through 2024-06-30. Exclude unmatched policyholders and claims.

Output

One row per region.
Return region and average_claim_amount, rounded to two decimal places.
Sort by region ascending, with NULL regions last.

Schema

policyholders

Column	Type	Description
policyholder_idPK	INT	Unique policyholder identifier
policyholder_name	VARCHAR(100)	Policyholder name
region	VARCHAR(50)	Policyholder geographic region
policy_start_date	DATE	Date the policy became effective

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
policyholder_id	INT	Referenced policyholder identifier
claim_date	DATE	Date the claim was filed
claim_amount	NUMERIC(12,2)	Claimed amount in dollars

Your question is Join Policies and Claims in SQL. 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.

Problem