Explain your approach to SQL optimization for large, complex healthcare datasets at Candid Health.
Demonstrate the approach by writing a query against the provided claims, provider, and payer data. Include only finalized claims submitted during Q1 2025.
Output
- One row per payer and submission month, with
service_month, payer_name, finalized_claims, denied_claims, denial_rate_pct, avg_processing_hours, and prior_month_denial_rate_pct.
- Exclude claims without a finalized adjudication or without matching provider and payer records.
- Sort by month ascending, then payer name ascending.
Output details
- Restrict claims to statuses
paid and denied, with non-NULL adjudicated_at values.
- Interpret Q1 2025 as submitted dates from
2025-01-01 inclusive through 2025-04-01 exclusive.
- Format
service_month as YYYY-MM.
- Round
denial_rate_pct and avg_processing_hours to two decimal places.
- Calculate each payer's prior rate from the preceding available submission month, returning NULL when none exists.
