Use CASE WHEN for rate calculation, LOWER() for normalization, and multi-table JOINs
Calculate the 30-day readmission rate by insurance type. Use the readmission_flag in quality metrics. Normalize insurance type so 'Medicare' and 'medicare' are grouped together. Show insurance type, total encounters with a readmission metric, readmissions, and readmission rate as a percentage rounded to 1 decimal.
| 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 |
| 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 |
|---|---|---|
| patient_id | INT | PK |
| first_name | TEXT | |
| last_name | TEXT | |
| date_of_birth | TEXT | |
| gender | TEXT | |
| insurance_type | TEXT | |
| zip_code | TEXT | |
| primary_language | TEXT | |
| is_deceased | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Three tables chain together: the metric links to the encounter, which links to the patient. Filter the metric_type upfront
Compute two quantities per group — totals and readmissions — and divide. Normalize the grouping key so casing doesn't split rows
`SELECT LOWER(p.insurance_type) AS insurance_type, COUNT(*) AS total_encounters, SUM(CASE WHEN qm.metric_value = 1 THEN ___ ELSE ___ END) AS readmissions, ROUND(100.0 * SUM(CASE WHEN qm.metric_value = 1 THEN 1 ELSE 0 END) / ___, 1) AS readmission_rate ... GROUP BY LOWER(___)`