Calculate rates with HAVING filter and handle sensitive data awareness
Before you share this with ANYONE — this is sensitive. For each active provider, show their complication rate per 100 encounters. Only include providers with at least 20 encounters so we're not flagging someone over 2 cases. Show name, specialty, encounter count, complications, and rate. Exclude inactive providers — some still have encounters in the system.
| Column | Type | Key |
|---|---|---|
| provider_id | INT | PK |
| first_name | TEXT | |
| last_name | TEXT | |
| specialty | TEXT | |
| department_id | INT | FK → dim_departments |
| hire_date | TEXT | |
| is_active | INT |
| Column | Type | Key |
|---|---|---|
| encounter_id | INT | PK |
| patient_id | INT | FK → dim_patients |
| provider_id | INT | FK → dim_providers |
| department_id | INT | FK → dim_departments |
| admit_date | TEXT | |
| discharge_date | TEXT | |
| diagnosis_code | TEXT | |
| diagnosis_description | TEXT | |
| encounter_type | TEXT | |
| discharge_disposition | TEXT |
| Column | Type | Key |
|---|---|---|
| metric_id | INT | PK |
| encounter_id | INT | FK → fact_encounters |
| patient_id | INT | FK → dim_patients |
| metric_type | TEXT | |
| metric_value | REAL | |
| measurement_date | TEXT | |
| reported_by | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Three tables: provider → encounter → quality metric. Filter to active providers and the complication metric type before aggregating
Rate per 100 = complications ÷ encounters × 100. Post-aggregation volume filter belongs in HAVING, not WHERE
`ROUND(100.0 * SUM(CASE WHEN qm.metric_value = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT e.encounter_id), 1)` — add `HAVING COUNT(DISTINCT e.encounter_id) ___ 20`