Refactoring a Slow Query | Dataford Interview Questions

Describe a time you had to refactor a slow-performing query at Candid Health.

For this exercise, demonstrate the refactored PostgreSQL query against the provided claims data. Limit the analysis to claims submitted during 2024 and return monthly payer-level operational metrics.

Output

One row per payer and submission month
Columns: payer_name, submission_month, submitted_claims, resolved_claims, total_paid, denial_rate_pct, and prior_month_paid
Include claims with no payment or incomplete payer details
Sort by payer name ascending, with missing payer names last, then submission month ascending

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
payer_id	INT	Identifier of the payer associated with the claim
submitted_at	TIMESTAMP	Timestamp when the claim was submitted
billed_amount	NUMERIC(12,2)	Amount billed on the claim

claim_events

Column	Type	Description
event_idPK	INT	Unique event identifier
claim_id	INT	Claim associated with the event
event_type	VARCHAR(30)	Claim lifecycle status
event_at	TIMESTAMP	Timestamp when the event occurred

payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
claim_id	INT	Claim associated with the payment
paid_at	DATE	Date the payment was recorded
amount	NUMERIC(12,2)	Payment amount, which may be unknown

payers

Column	Type	Description
payer_idPK	INT	Unique payer identifier
payer_name	VARCHAR(100)	Payer display name

Your question is Refactoring a Slow Query. Start with the requirements and the four 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