Who is still in a bed?
At the end of June 2026 the hospital director wants to know, per department, how many admissions are still open (the patient has not been discharged yet, so discharge_date is NULL) and how many are closed. A single query with CASE should answer for all departments.
Task: return department (the department name), still_admitted, discharged and total (all admissions of that department ever recorded). Sort by still_admitted from high to low, then by department A to Z. Every department must appear.
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?
NULL is not equal to anything, so test it with IS NULL inside the CASE.
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.