Your question is Analyzing Operations With SQL. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Write a SQL query to analyze operational performance by joining member, claims, and provider tables, then calculate monthly completion rates and identify the top underperforming regions. Treat claims with status completed as completed, and use service_date to determine the month. Return the two lowest-performing regions for each month, breaking ties by region name.
claim_month, region, total_claims, completed_claims, completion_rate, and underperformance_rank| Column | Type | Description |
|---|---|---|
| member_idPK | INT | Unique member identifier |
| member_name | VARCHAR(100) | Member name |
| plan_name | VARCHAR(100) | AmeriHealth Caritas plan name |
| Column | Type | Description |
|---|---|---|
| claim_idPK | INT | Unique claim identifier |
| member_id | INT | Referenced member identifier |
| provider_id | INT | Referenced provider identifier |
| service_date | DATE | Date of claim service |
| claim_status | VARCHAR(30) | Claim processing status |
| Column | Type | Description |
|---|---|---|
| provider_idPK | INT | Unique provider identifier |
| provider_name | VARCHAR(120) | Provider name |
| region | VARCHAR(50) | Provider operating region |