Overlapping Policy Coverage SQL | Dataford Interview Questions

Write a SQL query using window functions to identify overlapping insurance policy coverage periods for a single claimant.

Use the insurance_policies table and evaluate claimant 101. Coverage periods are inclusive, so a policy starting on the same date another policy ends is overlapping. Treat chained overlaps as one overlapping group.

Output

One row per policy belonging to an overlapping group.
Columns: claimant_id, overlap_group, policy_id, policy_number, coverage_start, coverage_end, and overlap_count.
Exclude policies that do not overlap another policy, and order by coverage_start, coverage_end, then policy_id.

Schema

insurance_policies

Column	Type	Description
policy_idPK	INT	Unique identifier for the policy
claimant_id	INT	Identifier of the claimant covered by the policy
policy_number	VARCHAR(30)	External insurance policy reference
coverage_start	DATE	Inclusive start date of coverage
coverage_end	DATE	Inclusive end date of coverage

Your question is Overlapping Policy Coverage SQL. Start with the requirements and the one table 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

Write a SQL query using window functions to identify overlapping insurance policy coverage periods for a single claimant.

Output

One row per policy belonging to an overlapping group.
Columns: claimant_id, overlap_group, policy_id, policy_number, coverage_start, coverage_end, and overlap_count.
Exclude policies that do not overlap another policy, and order by coverage_start, coverage_end, then policy_id.

Schema

insurance_policies

Column	Type	Description
policy_idPK	INT	Unique identifier for the policy
claimant_id	INT	Identifier of the claimant covered by the policy
policy_number	VARCHAR(30)	External insurance policy reference
coverage_start	DATE	Inclusive start date of coverage
coverage_end	DATE	Inclusive end date of coverage

Your question is Overlapping Policy Coverage SQL. Start with the requirements and the one table 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.