SQL Claims Delay Analysis | Dataford Interview Questions

Write a SQL query to analyze claims processing delays by region and month, joining claims and operations tables and calculating average delay and delayed-claim rate.

Treat a claim as delayed when processing takes more than 7 days. Include claims without a matching operation, but exclude them from the average-delay and delayed-rate denominator.

Output

Return one row per region and claim-submission month.
Include region, claim_month, average_delay_days, and delayed_claim_rate.
Express claim_month as YYYY-MM and the rate as a percentage.
Sort by region, then month chronologically.

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
region	VARCHAR(50)	Claims processing region
submitted_date	DATE	Date the claim was submitted

operations

Column	Type	Description
operation_idPK	INT	Unique operations record identifier
claim_id	INT	Claim processed by the operation
processed_date	DATE	Date the claim was processed

Your question is SQL Claims Delay Analysis. 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