One Hospital Resource Limits Everything: Beds. A SQL Deep Dive

Every hospital runs on the same constraint. If a hospital has 100 beds, it can help 100 people. The 101st patient waits, gets transferred, or goes somewhere else.

Adding beds takes years and serious capital. The faster lever is time: if patients who no longer need a bed go home sooner, that bed goes to someone who does. That is why length of stay is one of the most watched numbers in hospital operations. It is a care problem and a financial problem at the same time.

I wanted to see what that looks like in real data. So I took a well-known public healthcare dataset, the Diabetes 130-US Hospitals dataset from the UCI Machine Learning Repository, and loaded it into MySQL. It contains 101,766 hospital encounters from 130 US hospitals between 1999 and 2008.

One detail matters before any query runs: each row is an encounter, not a patient. The same person can appear more than once if they were hospitalized more than once. Every number in this analysis counts hospital visits, not people.

With the data loaded, I asked five questions about time in the hospital. Here's what I found.

---

About the Dataset & Tools

The Diabetes 130-US Hospitals dataset covers diabetes-related hospital encounters across 130 US facilities from 1999 to 2008. I loaded it into a MySQL database as two related tables: health (encounter-level clinical data: length of stay, procedures, medications) and demographics (patient attributes: race, gender, age), joined on the patient number.

Tools: MySQL · Data: Diabetes 130-US Hospitals for Years 1999–2008 (101,766 encounters), UCI Machine Learning Repository

---

Business Questions I Explored

Before writing a single query, I framed five questions:

1. How long do patients actually stay, and do most stays end within a week?
2. Do encounters with more lab procedures also run longer?
3. Which medical specialties drive the most procedures per encounter?
4. Does the average number of lab procedures differ by race?
5. How do the three main diabetes medications rank within each age group?

---

Q1 - How Long Do Patients Actually Stay?

What does the distribution of hospital stays look like, and do most end within a week?

SQL has no charts. But it can fake one. With ROUND to create buckets, COUNT to fill them, and RPAD to print one star per 100 encounters, you get a histogram made of text:

-- Q1: Length-of-stay distribution (1 star = 100 encounters)

SELECT ROUND(time_in_hospital, 1) AS stay_length_days,
COUNT(*) AS encounter_count,
RPAD('', COUNT(*)/100, '*') AS bar
FROM health
GROUP BY stay_length_days
ORDER BY stay_length_days;

The shape tells the story before any percentage does. Stays climb fast, peak at 3 days, then fall off in a long tail out to 14 days.

The numbers behind the shape: 80,617 encounters lasted less than 7 days. That is 79% of all hospital visits in the data, roughly four out of five.

For a hospital, that is mostly good news. Beds turn over quickly for the typical patient. But the tail is where the operational attention goes. I summed total bed-days on each side of the 7-day line:

-- Q1b: Bed-days consumed on each side of the 7-day line

SELECT
CASE WHEN time_in_hospital < 7 THEN 'under 7 days' ELSE '7 days or more' END AS stay_group,
COUNT(*) AS encounter_count,
SUM(time_in_hospital) AS total_bed_days,
ROUND(SUM(time_in_hospital) * 100.0 / (SELECT SUM(time_in_hospital) FROM health), 1) AS pct_of_all_bed_days
FROM health
GROUP BY stay_group;

The 21% of encounters lasting 7 days or more consumed 43.5% of all bed-days in the dataset. One in five visits used almost half the beds. Those long stays are exactly where a hospital wants to confirm the patient truly needs to be there.

---

Q2 - Do More Lab Procedures Mean Longer Stays?

Do encounters with more lab procedures also have a higher average time in the hospital?

The raw number of lab procedures per encounter ranges widely, so comparing it directly to length of stay is noisy. Instead, I binned encounters into three buckets with a CASE WHEN: fewer than 25 lab procedures ("few"), 25 to 54 ("average"), and 55 or more ("many"). These cutoffs are analysis buckets, not clinical thresholds.

-- Q2: Lab procedure buckets vs. average time in hospital

SELECT
CASE WHEN num_lab_procedures >= 0 AND num_lab_procedures < 25 THEN 'few'
     WHEN num_lab_procedures >= 25 AND num_lab_procedures < 55 THEN 'average'
     ELSE 'many'
END AS procedure_frequency,
ROUND(AVG(time_in_hospital), 2) AS avg_time_in_hospital_days,
COUNT(*) AS encounter_count
FROM health
GROUP BY procedure_frequency
ORDER BY avg_time_in_hospital_days DESC;

The pattern is consistent across all three groups. Encounters in the "many" bucket stayed 2.4 days longer on average than the "few" bucket, a 73% longer stay.

One caution before reading too much into this. This is a correlation, not a cause. Lab procedures probably don't make patients stay longer; sicker, more complex patients likely need more lab work and more time in a bed. But for operations, the pattern is still useful: lab procedure volume early in an encounter could serve as a rough signal for which patients are headed toward longer stays.

---

Q3 - Which Specialties Drive the Most Procedures?

Which medical specialties average the most procedures per encounter, at meaningful volume?

Procedures are a major cost driver. If a hospital wants to review procedure use, staffing, or scheduling, it shouldn't start with a list of every specialty in the building. It should start where procedures concentrate.

Two filters make that list trustworthy. First, only specialties averaging more than 2.5 procedures per encounter. Second, only specialties with more than 50 encounter records, because an average built on a handful of visits isn't an average worth acting on. Filtering on aggregated values is what HAVING does; WHERE can't touch them.

-- Q3: High-procedure specialties with real volume

SELECT medical_specialty,
ROUND(AVG(num_procedures), 1) AS avg_procedures_per_encounter,
COUNT(*) AS encounter_count
FROM health
GROUP BY medical_specialty
HAVING encounter_count > 50 AND avg_procedures_per_encounter > 2.5
ORDER BY avg_procedures_per_encounter DESC;

Out of dozens of specialties in the data, only five clear both bars. No surprises in who made the list: surgical and cardiac specialties are procedure-heavy by nature. The value isn't the surprise, it's the shortlist. Instead of reviewing every department, the hospital now has five places to look first, and one of them, cardiology, combines a high average with real volume: over 5,000 encounters.

---

Q4 - An Early Equity Check: Lab Procedures by Race

Does the average number of lab procedures per encounter differ by race?

Healthcare equity questions deserve serious analysis, and serious analysis starts with simple checks. Race lives in the demographics table and lab procedures live in the health table, so answering this requires a JOIN on the patient number:

-- Q4: Average lab procedures by race

SELECT d.race,
ROUND(AVG(h.num_lab_procedures), 1) AS avg_lab_procedures,
COUNT(*) AS encounter_count
FROM health AS h
JOIN demographics AS d ON h.patient_nbr = d.patient_nbr
GROUP BY d.race
ORDER BY avg_lab_procedures DESC;

The averages land in a narrow band: from 40.9 (Asian) to 44.1 (AfricanAmerican), a spread of about 3 lab procedures against averages in the low 40s. On this first look, there are no glaring gaps.

Three honest caveats about what this query can and cannot say:

• Group sizes are very unequal. Caucasian encounters number 76,107 while Asian encounters number 638, so the smaller groups carry more noise.
• 2,280 encounters have race recorded as '?'. Missing demographic data is itself worth flagging in any equity review.
• A similar average doesn't rule out differences. Groups could differ in age, diagnosis mix, or severity in ways a single average hides.

So the takeaway isn't "treatment is equal." It's narrower and more defensible: this simple screen found no large raw differences in lab procedure counts by race. A real equity analysis would control for clinical factors before drawing conclusions, and that's beyond a single GROUP BY.

---

Q5 - Ranking Diabetes Medications by Age Group

How do insulin, metformin, and glipizide rank by usage within each age group?

This dataset stores each medication in its own column: one for insulin, one for metformin, one for glipizide. That wide format makes ranking awkward, because the thing I want to rank, the medication name, is trapped in column headers instead of living in a column of its own.

The fix is to reshape the data from wide to long: stack the three medication columns into one, using UNION ALL. Then wrap that reshaped table in a CTE, join it to demographics to get age, and rank within each age group using a window function.

-- Q5: Reshape wide to long, then rank medications within each age group

WITH long_health_cte AS (
SELECT patient_nbr, 'metformin' AS medication, metformin AS med_usage FROM health
UNION ALL
SELECT patient_nbr, 'glipizide' AS medication, glipizide AS med_usage FROM health
UNION ALL
SELECT patient_nbr, 'insulin' AS medication, insulin AS med_usage FROM health
)
SELECT demographics.age,
long_health_cte.medication,
COUNT(*) AS encounter_use_count,
RANK() OVER (PARTITION BY demographics.age ORDER BY COUNT(*) DESC) AS medication_rank
FROM long_health_cte
JOIN demographics ON long_health_cte.patient_nbr = demographics.patient_nbr
WHERE med_usage NOT IN ('No')
GROUP BY medication, age
ORDER BY age, medication_rank;

Two details matter here. UNION ALL, not UNION, because we're counting encounter records and don't want duplicates silently removed before the count. And PARTITION BY age, which restarts the ranking inside each age bracket instead of ranking across the whole table.

Insulin ranks first in every single age group, usually by a wide margin. No surprise in a dataset of diabetes-related hospital encounters: inpatient settings lean heavily on insulin.

The interesting movement is underneath. Metformin holds second place through every age bracket from the teens to the seventies. Then, at ages 80–89, glipizide overtakes it, and the flip holds again for 90–99. In the two oldest brackets, the number-two diabetes medication changes.

I'll stay in my lane on the why: this is usage data, not clinical reasoning, and prescribing decisions for elderly patients involve factors this table doesn't capture. But finding the flip is exactly what this query structure is for. A wide table hides it; a long, ranked table surfaces it in one read.

---

Key Findings

• 79% of hospital encounters end in under 7 days, peaking at a 3-day stay
• The 21% of stays lasting 7+ days consumed 43.5% of all bed-days
• Encounters with 55+ lab procedures averaged 5.66 days in the hospital vs. 3.28 for low-procedure encounters, a 73% longer stay
• Only 5 specialties average more than 2.5 procedures per encounter at meaningful volume, led by thoracic surgery and anchored by cardiology's 5,352 encounters
• Average lab procedures by race sit in a narrow band (40.9 to 44.1), with no large raw gaps on a first-pass screen
• Insulin ranks #1 in every age group; at ages 80+, glipizide overtakes metformin as the #2 medication

---

Closing Thoughts

Start to finish, this analysis moved from a simple distribution to a windowed ranking, and each query narrowed the picture. None of these findings required anything exotic: GROUP BY, HAVING, a JOIN, a CASE WHEN, a CTE, and one window function. The skill isn't the syntax; it's knowing which question each tool answers, and what the result doesn't prove.

This dataset is best known for one column I barely touched: readmitted. Every encounter records whether the patient came back within 30 days, after 30 days, or not at all. Readmissions are the other side of the bed problem: discharging patients quickly frees beds, but discharging them too quickly sends them back. Whether short stays predict readmission is the natural next question, and it's where this analysis goes next.