4. Exploring Medicare Hospital Readmission Rates with SQL

By /

Now that the Medicare Hospital Readmission dataset is clean, it’s time to explore the data. In this post, we answer practical business questions that healthcare analysts commonly face.

Business Questions We Will Answer:

  • How do predicted and expected readmission rates compare across hospitals?
  • How many hospitals are included in the dataset?
  • Which states are represented and how many hospitals are in each state?
  • Which readmission measures appear most frequently?
  • What are the average, highest, and lowest readmission rates?

1. Total Number of Hospitals

SELECT COUNT(DISTINCT Facility_ID) AS TotalHospitals
FROM Hospital_Readmissions;

Returns the total count of unique hospitals in the dataset.

2. Total Number of States

SELECT COUNT(DISTINCT State) AS TotalStates
FROM Hospital_Readmissions;

Shows how many states are represented in the Medicare dataset.

3. Readmission Measures Distribution

SELECT 
	Measure_Name,
	COUNT(*) AS TotalRecords
FROM Hospital_Readmissions
GROUP BY Measure_Name
ORDER BY TotalRecords DESC;

Shows the frequency of each readmission measure evaluated by Medicare.

4. Average Excess Readmission Ratio

SELECT
    AVG(Excess_Readmission_Ratio) AS AverageExcessRatio
FROM Hospital_Readmissions;

Calculates the average excess readmission ratio across all hospitals.

5. Hospitals with Highest Predicted Readmission Rates

Identifies the 10 hospitals with the highest predicted readmission rates.

SELECT TOP 10
    Facility_Name,
    State,
    Excess_Readmission_Ratio
FROM Hospital_Readmissions
ORDER BY Excess_Readmission_Ratio DESC;

6. Hospitals with Lowest Predicted Readmission Rates

SELECT TOP 10
    Facility_Name,
    State,
    Predicted_Readmission_Rate
FROM Hospital_Readmissions
ORDER BY Predicted_Readmission_Rate ASC;

Identifies the 10 hospitals with the lowest predicted readmission rates.

7. Number of Hospitals by State

SELECT
    State,
    COUNT(DISTINCT Facility_ID) AS Total_Hospitals
FROM Hospital_Readmissions
GROUP BY State
ORDER BY Total_Hospitals DESC;

This query introduces the GROUP BY clause and shows how many hospitals are located in each state.

8. Average Predicted Readmission Rate by State

SELECT
    State,
    AVG(Predicted_Readmission_Rate) AS Average_Predicted_Rate
FROM Hospital_Readmissions
GROUP BY State
ORDER BY Average_Predicted_Rate DESC;

This query calculates the average readmission rate for each state and is useful for creating maps or bar charts in Power BI.

9. Comparing Predicted vs Expected Readmission Rates

SELECT
    Facility_Name,
    State,
    Predicted_Readmission_Rate,
    Expected_Readmission_Rate,
    Predicted_Readmission_Rate - Expected_Readmission_Rate AS Difference
FROM Hospital_Readmissions;

This query shows the difference between predicted and expected readmission rates for each hospital.

18330 Records

10. SQL Aggregate Functions Summary

Aggregate functions are essential for summarizing healthcare data. In this post, we use COUNT, AVG, SUM, MIN, and MAX to better understand hospital readmission patterns.

-- 1. Total number of hospitals and records
SELECT 
    COUNT(DISTINCT Facility_ID) AS Total_Hospitals,
    COUNT(*) AS Total_Records
FROM Hospital_Readmissions;

-- 2. Average, Minimum, and Maximum Readmission Rate
SELECT 
    AVG(Predicted_Readmission_Rate) AS Avg_Readmission_Rate,
    MIN(Predicted_Readmission_Rate) AS Min_Readmission_Rate,
    MAX(Predicted_Readmission_Rate) AS Max_Readmission_Rate
FROM Hospital_Readmissions;

-- 3. Average Readmission Rate by State
SELECT 
    State,
    COUNT(DISTINCT Facility_ID) AS Number_of_Hospitals,
    AVG(Predicted_Readmission_Rate) AS Avg_Readmission_Rate
FROM Hospital_Readmissions
GROUP BY State
ORDER BY Avg_Readmission_Rate DESC;

Demonstrates the practical use of COUNT, AVG, MIN, and MAX functions on healthcare data.

Example:

SELECT State,
       AVG(Predicted_Readmission_Rate)
FROM Hospital_Readmissions
GROUP BY State;

What These Queries Teach:

  • How to summarize large healthcare datasets
  • How to compare performance across states
  • Foundation skills needed for Power BI dashboards

11. Key Insights

  • The dataset includes hospitals from many U.S. states.
  • Readmission rates vary across hospitals and medical measures.
  • Some hospitals have considerably higher predicted readmission rates than others.
  • Aggregating data by state provides a broader view of regional performance.
  • Comparing predicted and expected readmission rates helps identify areas that may warrant further investigation.

Scroll to Top