Length of stay month by month in 2025
The bed management team at Meridian Central and Lakeshore wants to see how long patients stay, month by month, to plan staffing for 2026. Only admissions that have a discharge_date can be measured. The stay is the number of days between admit_date and discharge_date.
Task: for discharged admissions with an admit_date in calendar year 2025, return admit_month (formatted YYYY-MM), admissions (how many) and avg_stay_days (average stay, ROUND(x, 2)). Sort by admit_month ascending.
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?
julianday() turns an ISO date into a day number, so subtracting two of them gives days.
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.