Which department sees the most abnormal lab results?
A lab result is abnormal when result_value is below ref_low or above ref_high (values exactly on a limit are normal). Each lab belongs to an admission, and each admission belongs to a department. The clinical audit team wants the abnormal rate per department, ignoring tiny samples.
Task: return department (name), tests (number of lab results), abnormal_tests and abnormal_pct (ROUND(100.0 * abnormal / tests, 2)) for departments with at least 5 lab results. Sort by abnormal_pct from high to low, then by department A to Z.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| admission_id INTEGER | patient_id INTEGER | doctor_id INTEGER | dept_id INTEGER | admit_date TEXT | discharge_date TEXT | diagnosis TEXT | total_cost_usd INTEGER |
|---|---|---|---|---|---|---|---|
| 1 | 39 | 14 | 2 | 2024-08-03 | 2024-08-11 | Tumor resection | 15320 |
| 2 | 11 | 6 | 6 | 2024-08-12 | 2024-08-15 | Poisoning | 4480 |
| 3 | 52 | 5 | 5 | 2024-09-17 | 2024-09-28 | Dehydration | 12560 |
| 4 | 25 | 11 | 5 | 2024-09-18 | 2024-09-27 | Asthma attack | 9390 |
| 5 | 5 | 11 | 5 | 2024-09-20 | 2024-09-29 | Dehydration | 12860 |
| 6 | 22 | 13 | 1 | 2024-10-16 | 2024-10-28 | Chest pain | 18330 |
Showing the first 6 of 99 rows.
| dept_id INTEGER | name TEXT | hospital TEXT |
|---|---|---|
| 1 | Cardiology | Meridian Central Hospital |
| 2 | Oncology | Meridian Central Hospital |
| 3 | Orthopedics | Lakeshore Medical Center |
| 4 | Neurology | Meridian Central Hospital |
| 5 | Pediatrics | Lakeshore Medical Center |
| 6 | Emergency Medicine | Lakeshore Medical Center |
| doctor_id INTEGER | name TEXT | dept_id INTEGER | country TEXT |
|---|---|---|---|
| 1 | Dr. Jakub Zielinski | 1 | Poland |
| 2 | Dr. Lucia Romero | 2 | Spain |
| 3 | Dr. Tunde Adeyemi | 3 | Nigeria |
| 4 | Dr. Rohan Nair | 4 | India |
| 5 | Dr. Elena Vidal | 5 | Spain |
| 6 | Dr. Thiago Pereira | 6 | Brazil |
Showing the first 6 of 15 rows.
| lab_id INTEGER | admission_id INTEGER | patient_id INTEGER | test_name TEXT | unit TEXT | lab_date TEXT | result_value REAL | ref_low REAL | ref_high REAL |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 39 | HbA1c | % | 2024-08-04 | 8.43 | 4 | 5.7 |
| 2 | 2 | 11 | Creatinine | mg/dL | 2024-08-12 | 1.04 | 0.6 | 1.3 |
| 3 | 3 | 52 | LDL Cholesterol | mg/dL | 2024-09-18 | 149.29 | 50 | 130 |
| 4 | 4 | 25 | LDL Cholesterol | mg/dL | 2024-09-18 | 92.08 | 50 | 130 |
| 5 | 5 | 5 | Creatinine | mg/dL | 2024-09-21 | 1.66 | 0.6 | 1.3 |
| 6 | 6 | 22 | LDL Cholesterol | mg/dL | 2024-10-16 | 172.15 | 50 | 130 |
Showing the first 6 of 110 rows.
| patient_id INTEGER | name TEXT | gender TEXT | birth_date TEXT | country TEXT |
|---|---|---|---|---|
| 1 | Ethan Reed | F | 1995-04-12 | United States |
| 2 | Hanna Nowak | F | 1972-04-23 | Poland |
| 3 | Karim Salem | M | 1976-06-17 | Egypt |
| 4 | Jules Girard | F | 1993-09-05 | France |
| 5 | Hannah Hartmann | F | 2017-04-08 | Germany |
| 6 | Lucia Navarro | F | 1974-06-27 | Spain |
Showing the first 6 of 60 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
Two joins to reach the department, and a conditional SUM for the abnormal count.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.