SQL Time-Constrained Joins | Dataford Interview Questions

Write a SQL query to join multiple tables and aggregate data based on specific time-based constraints.

Use the provided policyholders, policies, and claims tables. Return policyholders with policies active during Q1 2024, counting and summing approved claims recorded during that quarter.

Output

One row per qualifying policyholder with policyholder_id, full_name, active_policy_count, approved_claim_count, and approved_claim_amount.
Include qualifying policyholders with no approved claims, order by policyholder_id ascending, and report zero for missing claim totals.

Schema

policyholders

Column	Type	Description
policyholder_idPK	INT	Unique policyholder identifier
full_name	VARCHAR(100)	Policyholder's full name
email	VARCHAR(150)	Policyholder email address

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
policyholder_id	INT	References the policyholder
policy_number	VARCHAR(30)	Business policy number
effective_date	DATE	Policy start date
expiration_date	DATE	Policy end date, or NULL for an open-ended policy

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
policy_id	INT	References the policy associated with the claim
claim_date	DATE	Date the claim was recorded
claim_status	VARCHAR(20)	Claim processing status
claim_amount	DECIMAL(12,2)	Approved claim amount

Your question is SQL Time-Constrained Joins. 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