Methodology
This is a portfolio project built to demonstrate an end-to-end business/data analyst workflow: a realistic operational problem, a dataset built to match it, SQL analysis against that dataset, and a dashboard that presents the findings a hospital-network operations team would actually act on.
The brief
Meridian Health Network operates four hospital sites. Leadership wants to know: where is the network losing money and time, and which of those losses are actually fixable? That means separating noise from pattern in ER throughput, readmissions, and per-site cost efficiency — and being honest about which findings hold up under scrutiny and which don't.
Data & approach
All data is synthetically generated— there is no real patient or hospital data anywhere in this project. The generator (data/generate.py) produces ~151,000 encounters and ~20,000 daily staffing records across 4 sites and 7 departments over a 2-year window, with realistic operational patterns seeded directly into the generation logic (weekday/seasonal demand curves, staffing-driven wait times, a deliberately inefficient site, medical-cost inflation, and payer-mix effects) rather than invented afterward to match a chart.
The analysis runs as real SQL (data/queries/*.sql) against a SQLite database built from that data, executed once at build time (data/build_insights.py) and exported to the JSON this dashboard reads. Every chart on this site links back to the exact query that produced it — use the “View SQL” toggle on any chart, or on the findings below.
Findings
ER wait times track staffing shortfalls, not just volume
Average ER wait time roughly doubles on Mondays (network average ~104 min vs. ~52–58 min the rest of the week) and rises again through flu season (December–February). Both periods line up with a measurable dip in the ER staffing ratio — the same days the department is short-staffed relative to schedule are the days waits spike. That's a scheduling problem, not just a demand problem.
Recommendation:build a flexible/on-call staffing pool specifically for Monday ER shifts and the Dec–Feb window, rather than relying on the standard weekly schedule to absorb a predictable surge.
Cardiology carries the network's highest readmission rate
At 17.7%, Cardiology's 30-day readmission rate is meaningfully above every other inpatient department (next-highest is ICU at 13.8%, network average 11.7%). This is the single department worth a root-cause review before any network-wide readmission initiative.
Recommendation: pilot a post-discharge follow-up program (nurse call or remote monitoring in the first 7 days) specifically for Cardiology before rolling it out network-wide.
Medicaid's elevated readmission risk is invisible until you control for department
A naive, network-wide breakdown of readmission rate by payer type shows Medicare and Medicaid essentially tied (~12.7% each) — nothing to act on. But that view is confounded: Medicare-heavy departments (Cardiology, ICU, Oncology) happen to also run higher base readmission rates for clinical reasons unrelated to payer. Once the same query is grouped by department first, Medicaid patients have the highestreadmission rate in every single one of the six inpatient departments — a pattern the aggregate view completely hides (a textbook Simpson's paradox).
Recommendation: report readmission-by-payer at the department level, not network-wide, and prioritize discharge-planning support (transportation, medication access, follow-up scheduling) for Medicaid patients regardless of department.
Riverside Medical Center is the network's cost and quality laggard
Riverside runs $8,043 average cost per case against a $5,760 average across the other three sites — about 40% higher — alongside the longest average length of stay (2.76 days vs. 2.14–2.26 elsewhere) and the highest readmission rate (15.8% vs. 9.6–10.8%). The gap is consistent across essentially every month in the two-year window (see the Site Comparison page), which rules out a one-time event and points to a structural efficiency problem at that site.
If Riverside's cost per case matched its peer average, the network would save roughly $43M annuallyon Riverside's current volume alone — illustrative given synthetic data, but the kind of number that justifies an operational efficiency review.
Recommendation: commission an operations audit at Riverside focused on length-of-stay drivers first, since LOS is the most direct lever on both cost and readmission risk.
Cost inflation is real, but process improvements are already offsetting it
Network-wide average cost per case rose over the two-year window, consistent with broad medical cost inflation. At the same time, Orthopedics and General Medicine both show a genuine downward trend in length of stay over the same period — the two departments where enhanced-recovery-style process changes were introduced. Their efficiency gains are partially offsetting the network's inflation exposure.
Recommendation: treat Orthopedics and General Medicine as the internal case study for extending length-of-stay process improvements to the departments (and the site) that need it most.