Data Cleaning and SQL Query Log

Project Purpose

The SQL portion of this project was designed to combine three separate CMS hospital datasets into a consistent Illinois hospital-level dataset that could support both exploratory analysis and Tableau visualization.

I used Google BigQuery to:

• validate the size and overlap of the source datasets;

• inspect available CMS and HCAHPS measures;

• clean text, numeric, and date fields;

• preserve unavailable CMS values as NULL;

• reshape long-format HCAHPS and readmission data into one row per hospital;

• compare hospital ratings and readmission performance;

• calculate correlations between patient-experience measures and readmission rates;

• compare hospital ownership groups; and

• identify top- and bottom-rated hospitals.

The three CMS datasets used were:

Hospital General Information

Patient Survey (HCAHPS) – Hospital

Unplanned Hospital Visits

All analyses were limited to Illinois facilities.

Phase 1: Validate the Source Data

Query 01a — Verify Row Counts and Unique Facilities

Before cleaning or joining the datasets, I checked the number of total rows and unique hospitals represented in each source.

SELECT

  'stg_patient_survey' AS table_name,

  COUNT(*) AS total_rows,

  COUNT(DISTINCT `Facility ID`) AS unique_facilities

FROM `hospital-readmissions-quality.hospital_quality.hcahps_surveys_IL`

UNION ALL

SELECT

  'stf_general_info' AS table_name,

  COUNT(*) AS total_rows,

  COUNT(DISTINCT `Facility ID`) AS unique_facilities

FROM `hospital-readmissions-quality.hospital_quality.hospital_info`

WHERE State = 'IL'

UNION ALL

SELECT

  'stg_unplanned_visits' AS table_name,

  COUNT(*) AS total_rows,

  COUNT(DISTINCT `Facility ID`) AS unique_facilities

FROM `hospital-readmissions-quality.hospital_quality.unplanned_visits`

WHERE State = 'IL';

Results

HCAHPS Patient Survey:

12,036 rows / 177 unique facilities

Hospital General Information:

194 rows / 194 unique facilities

Unplanned Hospital Visits:

2,478 rows / 177 unique facilities

This established that the general hospital dataset contained 17 more Illinois facilities than the two analytical datasets.

Query 01b — Check Facility Overlap

I next verified whether the same hospitals appeared across all three datasets.

The Hospital General Information dataset was used as the base because it contained the complete set of 194 Illinois facilities.

WITH il_hosp_info AS (

  SELECT DISTINCT

    `Facility ID`

  FROM `hospital-readmissions-quality.hospital_quality.hospital_info`

  WHERE State = 'IL'

),

il_unplanned AS (

  SELECT DISTINCT

    `Facility ID`

  FROM `hospital-readmissions-quality.hospital_quality.unplanned_visits`

  WHERE State = 'IL'

),

il_survey AS (

  SELECT DISTINCT

    `Facility ID`

  FROM `hospital-readmissions-quality.hospital_quality.hcahps_surveys_IL`

)

SELECT

  COUNT(h.`Facility ID`) AS total_il_facilities,

  COUNT(u.`Facility ID`) AS total_il_unplanned_visits_facilities,

  COUNT(s.`Facility ID`) AS total_il_survey_facilities,

  COUNT(

    CASE

      WHEN u.`Facility ID` IS NOT NULL

       AND s.`Facility ID` IS NOT NULL

      THEN h.`Facility ID`

    END

  ) AS overlapping_facilities_all_three

FROM il_hosp_info AS h

LEFT JOIN il_unplanned AS u

  ON h.`Facility ID` = u.`Facility ID`

LEFT JOIN il_survey AS s

  ON h.`Facility ID` = s.`Facility ID`;

Results

Total Illinois facilities:

194

Facilities represented in Unplanned Visits:

177

Facilities represented in HCAHPS:

177

Facilities represented in all three datasets:

177

This validation was completed before building the final dataset so that missing information could be distinguished from failed joins.

Phase 2: Inspect the Available Measures

Query 01c — Inspect Schemas and Measure IDs

Before selecting fields for analysis, I reviewed the hospital metadata and the available readmission and HCAHPS measure IDs.

Hospital information

SELECT

  `Facility ID`,

  `Facility Name`,

  `Hospital Type`,

  `Hospital Ownership`,

  `Hospital overall rating`

FROM `hospital-readmissions-quality.hospital_quality.hospital_info`

WHERE State = 'IL'

LIMIT 10;

The sample was used to identify potential cleaning issues, particularly values such as "Not available" in fields that would later need to be numeric.

Unplanned Visit Measures

SELECT DISTINCT

  `Measure ID`,

  `Measure Name`

FROM `hospital-readmissions-quality.hospital_quality.unplanned_visits`

WHERE State = 'IL';

The source contained multiple unplanned-visit measures. For the readmission portion of the project, I selected six 30-day measures:

• READM_30_AMI — Acute Myocardial Infarction

• READM_30_CABG — CABG

• READM_30_COPD — COPD

• READM_30_HF — Heart Failure

• READM_30_HIP_KNEE — Hip/Knee Replacement

• READM_30_PN — Pneumonia

HCAHPS Survey Measures

SELECT DISTINCT

  `HCAHPS Measure ID`,

  `HCAHPS Question`

FROM `hospital-readmissions-quality.hospital_quality.hcahps_surveys_IL`;

This allowed me to identify the specific HCAHPS measures needed later for communication, cleanliness, quietness, discharge information, overall patient ratings, and willingness to recommend the hospital.

Phase 3: Create Cleaned Source Views

Rather than modifying the raw CMS tables, I created cleaned views. This allowed the original data to remain unchanged while giving the analytical queries standardized fields and data types.

Query 02a — Clean Hospital Information

CREATE OR REPLACE VIEW

  `hospital-readmissions-quality.hospital_quality.v_clean_hospital_info` AS

SELECT

  SAFE_CAST(`Facility ID` AS STRING) AS facility_id,

  TRIM(`Facility Name`) AS facility_name,

  TRIM(`Address`) AS address,

  TRIM(`City_Town`) AS city,

  TRIM(`State`) AS state,

  SAFE_CAST(`ZIP Code` AS STRING) AS zip_code,

  TRIM(`County_Parish`) AS county,

  TRIM(`Hospital Type`) AS hospital_type,

  TRIM(`Hospital Ownership`) AS hospital_ownership,

  -- Cast BOOL to STRING before applying TRIM

  TRIM(CAST(`Emergency Services` AS STRING)) AS emergency_services,

  -- Nonnumeric values such as "Not available" become NULL

  SAFE_CAST(`Hospital overall rating` AS INT64) AS overall_star_rating

FROM `hospital-readmissions-quality.hospital_quality.hospital_info`

WHERE State = 'IL';

Cleaning Decisions

Facility ID

Converted to STRING so the join key would use the same type across datasets.

Text fields

Used TRIM() to remove leading or trailing whitespace.

ZIP Code

Stored as STRING rather than numeric because ZIP codes are identifiers rather than quantities.

Emergency Services

Converted from BOOLEAN to STRING before applying TRIM().

Hospital Overall Rating

Converted to INT64.

Using SAFE_CAST() was important because CMS uses unavailable values in some fields. Instead of causing the query to fail, nonnumeric values became NULL.

Query 02b — Clean Unplanned Hospital Visits

CREATE OR REPLACE VIEW

  `hospital-readmissions-quality.hospital_quality.v_clean_unplanned_visits` AS

SELECT

  SAFE_CAST(`Facility ID` AS STRING) AS facility_id,

  TRIM(`Measure ID`) AS measure_id,

  TRIM(`Measure Name`) AS measure_name,

  TRIM(`Compared to National`) AS compared_to_national,

  SAFE_CAST(`Denominator` AS INT64) AS denominator,

  SAFE_CAST(`Score` AS FLOAT64) AS score,

  SAFE_CAST(`Lower Estimate` AS FLOAT64) AS lower_estimate,

  SAFE_CAST(`Higher Estimate` AS FLOAT64) AS higher_estimate,

  SAFE_CAST(`Number of Patients` AS INT64) AS number_of_patients,

  SAFE_CAST(`Number of Patients Returned` AS INT64)

    AS number_of_patients_returned,

  SAFE_CAST(`Start Date` AS DATE) AS start_date,

  SAFE_CAST(`End Date` AS DATE) AS end_date

FROM `hospital-readmissions-quality.hospital_quality.unplanned_visits`

WHERE State = 'IL';

Cleaning Decisions

The main purpose of this view was to convert readmission values from text-compatible source fields into usable analytical formats.

The following were converted to numeric values:

• denominator;

• score;

• lower estimate;

• higher estimate;

• number of patients; and

• number of patients returned.

Reporting-period dates were converted to DATE.

Unavailable CMS scores were allowed to become NULL. I did not replace them with averages, zeros, or estimated values.

Query 02c — Clean HCAHPS Survey Data

CREATE OR REPLACE VIEW

  `hospital-readmissions-quality.hospital_quality.v_clean_hcahps_surveys_IL` AS

SELECT

  SAFE_CAST(`Facility ID` AS STRING) AS facility_id,

  TRIM(`HCAHPS Measure ID`) AS hcahps_measure_id,

  TRIM(`HCAHPS Question`) AS hcahps_question,

  TRIM(`HCAHPS Answer Description`) AS answer_description,

  -- Numeric survey fields

  SAFE_CAST(`Patient Survey Star Rating` AS INT64) AS star_rating,

  SAFE_CAST(`HCAHPS Answer Percent` AS FLOAT64) AS answer_percent,

  SAFE_CAST(`HCAHPS Linear Mean Value` AS FLOAT64) AS linear_mean_value,

  SAFE_CAST(`Number of Completed Surveys` AS INT64) AS completed_surveys,

  SAFE_CAST(`Survey Response Rate Percent` AS INT64) AS response_rate_percent,

  SAFE_CAST(`Start Date` AS DATE) AS start_date,

  SAFE_CAST(`End Date` AS DATE) AS end_date

FROM `hospital-readmissions-quality.hospital_quality.hcahps_surveys_IL`

WHERE State = 'IL';

Cleaning Decisions

Patient Survey Star Ratings were converted to integers.

HCAHPS answer percentages and linear mean values were converted to floating-point values.

Completed-survey counts and response rates were converted to integers.

Dates were standardized as DATE.

As with the readmission dataset, unavailable values remained NULL.

Phase 4: Verify the Cleaned Views

Query 02d — Cross-Table Verification

After creating all three cleaned views, I joined a sample of records to make sure that:

• Facility IDs matched properly;

• hospital ratings were numeric;

• readmission scores were numeric;

• survey ratings were numeric; and

• dates were correctly converted.

SELECT

  h.facility_id,

  h.facility_name,

  h.overall_star_rating,

  u.measure_id,

  u.score AS readmission_score,

  u.start_date AS readmission_start_date,

  s.hcahps_measure_id,

  s.star_rating AS survey_star_rating

FROM

  `hospital-readmissions-quality.hospital_quality.v_clean_hospital_info` AS h

JOIN

  `hospital-readmissions-quality.hospital_quality.v_clean_unplanned_visits` AS u

ON h.facility_id = u.facility_id

JOIN

  `hospital-readmissions-quality.hospital_quality.v_clean_hcahps_surveys_IL` AS s

ON h.facility_id = s.facility_id

WHERE h.overall_star_rating IS NOT NULL

  AND u.score IS NOT NULL

  AND s.star_rating IS NOT NULL

LIMIT 10;

This was a QA query rather than the final data model.

Its purpose was to make sure the cleaned datasets could be joined successfully before moving into analysis.

Phase 5: Exploratory Data Analysis

Query 03a — CMS Overall Star Rating Distribution

SELECT

  overall_star_rating,

  COUNT(*) AS hospital_count,

  ROUND(

    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(),

    1

  ) AS pct_of_total

FROM

  `hospital-readmissions-quality.hospital_quality.v_clean_hospital_info`

GROUP BY overall_star_rating

ORDER BY overall_star_rating ASC;

Results

No numeric rating:

58 hospitals — 29.9%

1 star:

15 hospitals — 7.7%

2 stars:

29 hospitals — 14.9%

3 stars:

43 hospitals — 22.2%

4 stars:

35 hospitals — 18.0%

5 stars:

14 hospitals — 7.2%

A total of 136 of 194 hospitals had an available numeric CMS Overall Star Rating.

The remaining hospitals were retained in the master dataset rather than deleted.

Query 03b — Condition-Specific Readmission Summary

SELECT

  measure_id,

  measure_name,

  COUNT(DISTINCT facility_id) AS reporting_hospitals,

  ROUND(AVG(score), 2) AS avg_readmission_rate,

  ROUND(MIN(score), 2) AS min_readmission_rate,

  ROUND(MAX(score), 2) AS max_readmission_rate,

  ROUND(STDDEV(score), 2) AS stddev_readmission_rate

FROM

  `hospital-readmissions-quality.hospital_quality.v_clean_unplanned_visits`

WHERE measure_id LIKE 'READM_30%'

GROUP BY

  measure_id,

  measure_name

ORDER BY avg_readmission_rate DESC;

Key Findings

Heart Failure had the highest average 30-day readmission rate:

19.92%

COPD followed at:

18.39%

Hip/Knee Replacement had the lowest average:

5.06%

The available hip/knee values ranged from approximately:

4.1% to 7.8%

Because unavailable source scores were converted to NULL, the presence of a hospital in the unplanned-visits dataset does not necessarily mean that hospital contributes a numeric value to every condition-specific analysis.

Query 03c — HCAHPS Star Rating Summary

SELECT

  hcahps_measure_id,

  hcahps_question,

  COUNT(DISTINCT facility_id) AS reporting_hospitals,

  ROUND(AVG(star_rating), 2) AS avg_star_rating,

  MIN(star_rating) AS min_star_rating,

  MAX(star_rating) AS max_star_rating

FROM

  `hospital-readmissions-quality.hospital_quality.v_clean_hcahps_surveys_IL`

WHERE hcahps_measure_id LIKE '%_STAR_RATING'

GROUP BY

  hcahps_measure_id,

  hcahps_question

ORDER BY avg_star_rating DESC;

Key Findings

Recommend Hospital had the highest reviewed average:

3.41 stars

Nurse Communication followed at:

3.26 stars

Communication About Medicines had the lowest average:

2.50 stars

Doctor Communication was the only reviewed domain for which the minimum hospital rating was 2 rather than 1.

Phase 6: Reshape the Data to One Row per Hospital

Query 04a — Create the Hospital Performance Fact View

The HCAHPS and unplanned-visits datasets were stored in long format. This meant that the same hospital appeared across multiple rows for different measures.

For Tableau and hospital-level statistical analysis, I needed the selected measures available as separate columns in a single hospital record.

I used conditional aggregation with:

MAX(CASE WHEN ... THEN ... END)

to pivot the selected HCAHPS and readmission measures.

CREATE OR REPLACE VIEW

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance` AS

WITH pivoted_survey AS (

  SELECT

    facility_id,

    -- HCAHPS Star Ratings

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_STAR_RATING'

        THEN star_rating

      END

    ) AS hcahps_summary_star_rating,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_HSP_RATING_STAR_RATING'

        THEN star_rating

      END

    ) AS hcahps_overall_star_rating,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_RECMND_STAR_RATING'

        THEN star_rating

      END

    ) AS hcahps_recommend_star_rating,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_COMP_1_STAR_RATING'

        THEN star_rating

      END

    ) AS hcahps_nurse_comm_star_rating,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_COMP_2_STAR_RATING'

        THEN star_rating

      END

    ) AS hcahps_doctor_comm_star_rating,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_COMP_5_STAR_RATING'

        THEN star_rating

      END

    ) AS hcahps_med_comm_star_rating,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_COMP_6_STAR_RATING'

        THEN star_rating

      END

    ) AS hcahps_discharge_info_star_rating,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_CLEAN_STAR_RATING'

        THEN star_rating

      END

    ) AS hcahps_cleanliness_star_rating,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_QUIET_STAR_RATING'

        THEN star_rating

      END

    ) AS hcahps_quietness_star_rating,

    -- HCAHPS Percentages

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_COMP_1_A_P'

        THEN answer_percent

      END

    ) AS nurse_comm_always_pct,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_COMP_2_A_P'

        THEN answer_percent

      END

    ) AS doctor_comm_always_pct,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_COMP_5_A_P'

        THEN answer_percent

      END

    ) AS med_comm_always_pct,

    MAX(

      CASE

        WHEN hcahps_measure_id = 'H_RECMND_DY'

        THEN answer_percent

      END

    ) AS recommend_definitely_pct

  FROM

    `hospital-readmissions-quality.hospital_quality.v_clean_hcahps_surveys_IL`

  GROUP BY facility_id

),

pivoted_readmissions AS (

  SELECT

    facility_id,

    MAX(

      CASE

        WHEN measure_id = 'READM_30_HF'

        THEN score

      END

    ) AS readm_rate_heart_failure,

    MAX(

      CASE

        WHEN measure_id = 'READM_30_COPD'

        THEN score

      END

    ) AS readm_rate_copd,

    MAX(

      CASE

        WHEN measure_id = 'READM_30_PN'

        THEN score

      END

    ) AS readm_rate_pneumonia,

    MAX(

      CASE

        WHEN measure_id = 'READM_30_AMI'

        THEN score

      END

    ) AS readm_rate_ami,

    MAX(

      CASE

        WHEN measure_id = 'READM_30_CABG'

        THEN score

      END

    ) AS readm_rate_cabg,

    MAX(

      CASE

        WHEN measure_id = 'READM_30_HIP_KNEE'

        THEN score

      END

    ) AS readm_rate_hip_knee

  FROM

    `hospital-readmissions-quality.hospital_quality.v_clean_unplanned_visits`

  GROUP BY facility_id

)

SELECT

  -- Hospital Metadata

  g.facility_id,

  g.facility_name,

  g.city,

  g.county,

  g.zip_code,

  g.hospital_type,

  g.hospital_ownership,

  g.emergency_services,

  g.overall_star_rating AS cms_overall_star_rating,

  -- Patient Survey Ratings and Metrics

  s.hcahps_summary_star_rating,

  s.hcahps_overall_star_rating,

  s.hcahps_recommend_star_rating,

  s.hcahps_nurse_comm_star_rating,

  s.hcahps_doctor_comm_star_rating,

  s.hcahps_med_comm_star_rating,

  s.hcahps_discharge_info_star_rating,

  s.hcahps_cleanliness_star_rating,

  s.hcahps_quietness_star_rating,

  s.nurse_comm_always_pct,

  s.doctor_comm_always_pct,

  s.med_comm_always_pct,

  s.recommend_definitely_pct,

  -- 30-Day Readmission Metrics

  r.readm_rate_heart_failure,

  r.readm_rate_copd,

  r.readm_rate_pneumonia,

  r.readm_rate_ami,

  r.readm_rate_cabg,

  r.readm_rate_hip_knee

FROM

  `hospital-readmissions-quality.hospital_quality.v_clean_hospital_info` AS g

LEFT JOIN pivoted_survey AS s

  ON g.facility_id = s.facility_id

LEFT JOIN pivoted_readmissions AS r

  ON g.facility_id = r.facility_id;

Why I Used LEFT JOIN

The cleaned general hospital table contains all 194 Illinois facilities.

Using it as the base table and applying LEFT JOINs allowed hospitals to remain in the master dataset even when they did not have available HCAHPS or readmission data.

Missing measures remained NULL.

This was preferable to using an INNER JOIN, which would have automatically removed hospitals lacking one of the selected datasets or measures.

Query 04b — Verify the Wide Hospital-Level Dataset

SELECT

  facility_id,

  facility_name,

  hospital_ownership,

  cms_overall_star_rating,

  hcahps_nurse_comm_star_rating,

  hcahps_med_comm_star_rating,

  readm_rate_heart_failure,

  readm_rate_copd

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

WHERE cms_overall_star_rating IS NOT NULL

LIMIT 5;

This confirmed that hospital metadata, HCAHPS measures, and condition-specific readmission rates were available within the same hospital-level record.

The resulting wide table became the primary source for the remaining SQL analyses and the Tableau dashboard.

Phase 7: Compare Communication Domains With Readmissions

Query 05b — Communication Domain Summary

This query compared hospitals at different HCAHPS communication-star levels with their average condition-specific readmission rates.

SELECT

  'Nurse Communication' AS communication_domain,

  hcahps_nurse_comm_star_rating AS star_rating,

  COUNT(facility_id) AS hospital_count,

  ROUND(AVG(readm_rate_heart_failure), 2) AS avg_hf_readmission_rate,

  ROUND(AVG(readm_rate_copd), 2) AS avg_copd_readmission_rate,

  ROUND(AVG(readm_rate_pneumonia), 2) AS avg_pn_readmission_rate,

  ROUND(AVG(readm_rate_ami), 2) AS avg_ami_readmission_rate,

  ROUND(AVG(readm_rate_cabg), 2) AS avg_cabg_readmission_rate,

  ROUND(AVG(readm_rate_hip_knee), 2) AS avg_hip_knee_readmission_rate

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

WHERE hcahps_nurse_comm_star_rating IS NOT NULL

GROUP BY hcahps_nurse_comm_star_rating

UNION ALL

SELECT

  'Doctor Communication' AS communication_domain,

  hcahps_doctor_comm_star_rating AS star_rating,

  COUNT(facility_id) AS hospital_count,

  ROUND(AVG(readm_rate_heart_failure), 2) AS avg_hf_readmission_rate,

  ROUND(AVG(readm_rate_copd), 2) AS avg_copd_readmission_rate,

  ROUND(AVG(readm_rate_pneumonia), 2) AS avg_pn_readmission_rate,

  ROUND(AVG(readm_rate_ami), 2) AS avg_ami_readmission_rate,

  ROUND(AVG(readm_rate_cabg), 2) AS avg_cabg_readmission_rate,

  ROUND(AVG(readm_rate_hip_knee), 2) AS avg_hip_knee_readmission_rate

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

WHERE hcahps_doctor_comm_star_rating IS NOT NULL

GROUP BY hcahps_doctor_comm_star_rating

UNION ALL

SELECT

  'Medicine Communication' AS communication_domain,

  hcahps_med_comm_star_rating AS star_rating,

  COUNT(facility_id) AS hospital_count,

  ROUND(AVG(readm_rate_heart_failure), 2) AS avg_hf_readmission_rate,

  ROUND(AVG(readm_rate_copd), 2) AS avg_copd_readmission_rate,

  ROUND(AVG(readm_rate_pneumonia), 2) AS avg_pn_readmission_rate,

  ROUND(AVG(readm_rate_ami), 2) AS avg_ami_readmission_rate,

  ROUND(AVG(readm_rate_cabg), 2) AS avg_cabg_readmission_rate,

  ROUND(AVG(readm_rate_hip_knee), 2) AS avg_hip_knee_readmission_rate

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

WHERE hcahps_med_comm_star_rating IS NOT NULL

GROUP BY hcahps_med_comm_star_rating

UNION ALL

SELECT

  'Discharge Information' AS communication_domain,

  hcahps_discharge_info_star_rating AS star_rating,

  COUNT(facility_id) AS hospital_count,

  ROUND(AVG(readm_rate_heart_failure), 2) AS avg_hf_readmission_rate,

  ROUND(AVG(readm_rate_copd), 2) AS avg_copd_readmission_rate,

  ROUND(AVG(readm_rate_pneumonia), 2) AS avg_pn_readmission_rate,

  ROUND(AVG(readm_rate_ami), 2) AS avg_ami_readmission_rate,

  ROUND(AVG(readm_rate_cabg), 2) AS avg_cabg_readmission_rate,

  ROUND(AVG(readm_rate_hip_knee), 2) AS avg_hip_knee_readmission_rate

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

WHERE hcahps_discharge_info_star_rating IS NOT NULL

GROUP BY hcahps_discharge_info_star_rating

ORDER BY

  communication_domain,

  star_rating DESC;

This provided an initial grouped comparison before moving into continuous correlation and regression analyses.

Phase 8: Correlation Analysis

Query 05c — Patient Experience vs. Readmission Correlation Matrix

I used BigQuery's CORR() function to calculate Pearson correlations between six HCAHPS experience measures and each of the six selected readmission measures.

WITH domain_correlations AS (

SELECT

  'Nurse Communication' AS communication_domain,

  ROUND(CORR(

    hcahps_nurse_comm_star_rating,

    readm_rate_heart_failure

  ), 3) AS corr_heart_failure,

  ROUND(CORR(

    hcahps_nurse_comm_star_rating,

    readm_rate_copd

  ), 3) AS corr_copd,

  ROUND(CORR(

    hcahps_nurse_comm_star_rating,

    readm_rate_pneumonia

  ), 3) AS corr_pneumonia,

  ROUND(CORR(

    hcahps_nurse_comm_star_rating,

    readm_rate_ami

  ), 3) AS corr_ami,

  ROUND(CORR(

    hcahps_nurse_comm_star_rating,

    readm_rate_cabg

  ), 3) AS corr_cabg,

  ROUND(CORR(

    hcahps_nurse_comm_star_rating,

    readm_rate_hip_knee

  ), 3) AS corr_hip_knee

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

UNION ALL

SELECT

  'Doctor Communication' AS communication_domain,

  ROUND(CORR(

    hcahps_doctor_comm_star_rating,

    readm_rate_heart_failure

  ), 3) AS corr_heart_failure,

  ROUND(CORR(

    hcahps_doctor_comm_star_rating,

    readm_rate_copd

  ), 3) AS corr_copd,

  ROUND(CORR(

    hcahps_doctor_comm_star_rating,

    readm_rate_pneumonia

  ), 3) AS corr_pneumonia,

  ROUND(CORR(

    hcahps_doctor_comm_star_rating,

    readm_rate_ami

  ), 3) AS corr_ami,

  ROUND(CORR(

    hcahps_doctor_comm_star_rating,

    readm_rate_cabg

  ), 3) AS corr_cabg,

  ROUND(CORR(

    hcahps_doctor_comm_star_rating,

    readm_rate_hip_knee

  ), 3) AS corr_hip_knee

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

UNION ALL

SELECT

  'Medicine Communication' AS communication_domain,

  ROUND(CORR(

    hcahps_med_comm_star_rating,

    readm_rate_heart_failure

  ), 3) AS corr_heart_failure,

  ROUND(CORR(

    hcahps_med_comm_star_rating,

    readm_rate_copd

  ), 3) AS corr_copd,

  ROUND(CORR(

    hcahps_med_comm_star_rating,

    readm_rate_pneumonia

  ), 3) AS corr_pneumonia,

  ROUND(CORR(

    hcahps_med_comm_star_rating,

    readm_rate_ami

  ), 3) AS corr_ami,

  ROUND(CORR(

    hcahps_med_comm_star_rating,

    readm_rate_cabg

  ), 3) AS corr_cabg,

  ROUND(CORR(

    hcahps_med_comm_star_rating,

    readm_rate_hip_knee

  ), 3) AS corr_hip_knee

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

UNION ALL

SELECT

  'Discharge Information' AS communication_domain,

  ROUND(CORR(

    hcahps_discharge_info_star_rating,

    readm_rate_heart_failure

  ), 3) AS corr_heart_failure,

  ROUND(CORR(

    hcahps_discharge_info_star_rating,

    readm_rate_copd

  ), 3) AS corr_copd,

  ROUND(CORR(

    hcahps_discharge_info_star_rating,

    readm_rate_pneumonia

  ), 3) AS corr_pneumonia,

  ROUND(CORR(

    hcahps_discharge_info_star_rating,

    readm_rate_ami

  ), 3) AS corr_ami,

  ROUND(CORR(

    hcahps_discharge_info_star_rating,

    readm_rate_cabg

  ), 3) AS corr_cabg,

  ROUND(CORR(

    hcahps_discharge_info_star_rating,

    readm_rate_hip_knee

  ), 3) AS corr_hip_knee

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

UNION ALL

SELECT

  'Cleanliness' AS communication_domain,

  ROUND(CORR(

    hcahps_cleanliness_star_rating,

    readm_rate_heart_failure

  ), 3) AS corr_heart_failure,

  ROUND(CORR(

    hcahps_cleanliness_star_rating,

    readm_rate_copd

  ), 3) AS corr_copd,

  ROUND(CORR(

    hcahps_cleanliness_star_rating,

    readm_rate_pneumonia

  ), 3) AS corr_pneumonia,

  ROUND(CORR(

    hcahps_cleanliness_star_rating,

    readm_rate_ami

  ), 3) AS corr_ami,

  ROUND(CORR(

    hcahps_cleanliness_star_rating,

    readm_rate_cabg

  ), 3) AS corr_cabg,

  ROUND(CORR(

    hcahps_cleanliness_star_rating,

    readm_rate_hip_knee

  ), 3) AS corr_hip_knee

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

UNION ALL

SELECT

  'Quietness' AS communication_domain,

  ROUND(CORR(

    hcahps_quietness_star_rating,

    readm_rate_heart_failure

  ), 3) AS corr_heart_failure,

  ROUND(CORR(

    hcahps_quietness_star_rating,

    readm_rate_copd

  ), 3) AS corr_copd,

  ROUND(CORR(

    hcahps_quietness_star_rating,

    readm_rate_pneumonia

  ), 3) AS corr_pneumonia,

  ROUND(CORR(

    hcahps_quietness_star_rating,

    readm_rate_ami

  ), 3) AS corr_ami,

  ROUND(CORR(

    hcahps_quietness_star_rating,

    readm_rate_cabg

  ), 3) AS corr_cabg,

  ROUND(CORR(

    hcahps_quietness_star_rating,

    readm_rate_hip_knee

  ), 3) AS corr_hip_knee

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

)

SELECT *

FROM domain_correlations;

Interpretation

Negative correlations indicate that higher patient-experience ratings tend to occur alongside lower readmission rates.

Positive correlations indicate that both measures tend to increase together.

However, correlation alone does not establish causation.

I therefore used these results primarily to identify relationships worth examining more closely in Tableau, where trend lines provided R² values and statistical significance.

The larger regression review eventually showed that the patient-experience measures had very limited explanatory power for condition-specific readmission rates.

Across the 36 patient-experience/readmission combinations, none had an R² above approximately 0.083, and only four met the p ≤ 0.05 threshold.

Phase 9: Hospital Ownership Analysis

Query 05d — Compare Ownership, Ratings, and Readmissions

SELECT

  hospital_ownership,

  COUNT(facility_id) AS total_hospitals,

  ROUND(

    AVG(cms_overall_star_rating),

    2

  ) AS avg_cms_overall_rating,

  ROUND(

    AVG(hcahps_summary_star_rating),

    2

  ) AS avg_hcahps_summary_rating,

  -- Communication Domains

  ROUND(

    AVG(hcahps_med_comm_star_rating),

    2

  ) AS avg_med_comm_rating,

  ROUND(

    AVG(hcahps_doctor_comm_star_rating),

    2

  ) AS avg_doc_comm_rating,

  ROUND(

    AVG(hcahps_nurse_comm_star_rating),

    2

  ) AS avg_nurse_comm_rating,

  ROUND(

    AVG(hcahps_discharge_info_star_rating),

    2

  ) AS avg_discharge_info_rating,

  -- Readmission Rates

  ROUND(

    AVG(readm_rate_heart_failure),

    2

  ) AS avg_hf_readm_rate,

  ROUND(

    AVG(readm_rate_ami),

    2

  ) AS avg_ami_readm_rate,

  ROUND(

    AVG(readm_rate_pneumonia),

    2

  ) AS avg_pn_readm_rate,

  ROUND(

    AVG(readm_rate_copd),

    2

  ) AS avg_copd_readm_rate,

  ROUND(

    AVG(readm_rate_hip_knee),

    2

  ) AS avg_hip_knee_readm_rate,

  ROUND(

    AVG(readm_rate_cabg),

    2

  ) AS avg_cabg_readm_rate

FROM

  `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

GROUP BY hospital_ownership

HAVING total_hospitals >= 3

ORDER BY avg_cms_overall_rating DESC;

Purpose

This query compared hospital ownership categories using:

• CMS Overall Star Rating;

• HCAHPS Summary Star Rating;

• nurse communication;

• physician communication;

• medication communication;

• discharge information; and

• all six selected readmission rates.

Ownership groups containing fewer than three hospitals were excluded from this summary to avoid emphasizing extremely small categories.

The output was later incorporated into the Tableau ownership comparison.

Phase 10: Identify High- and Low-Rated Hospitals

Query 05e — Rank Hospitals by CMS Overall Star Rating

WITH ranked_hospitals AS (

  SELECT

    facility_id,

    facility_name,

    city,

    hospital_ownership,

    cms_overall_star_rating,

    DENSE_RANK() OVER (

      ORDER BY cms_overall_star_rating DESC NULLS LAST

    ) AS rank_top,

    DENSE_RANK() OVER (

      ORDER BY cms_overall_star_rating ASC NULLS LAST

    ) AS rank_bottom

  FROM

    `hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance`

  WHERE cms_overall_star_rating IS NOT NULL

)

SELECT

  'Top Performer' AS performance_category,

  facility_id,

  facility_name,

  city,

  cms_overall_star_rating

FROM ranked_hospitals

WHERE rank_top <= 5

UNION ALL

SELECT

  'Bottom Performer' AS performance_category,

  facility_id,

  facility_name,

  city,

  cms_overall_star_rating

FROM ranked_hospitals

WHERE rank_bottom <= 5

ORDER BY

  performance_category DESC,

  cms_overall_star_rating DESC;

Why DENSE_RANK Was Used

CMS Overall Star Ratings are ordinal values from 1 to 5, meaning many hospitals share the same rating.

Using DENSE_RANK() allowed ties to remain tied rather than assigning arbitrary sequential positions to hospitals with identical ratings.

Hospitals with NULL CMS Overall Star Ratings were excluded because an unavailable rating cannot meaningfully be ranked.

Data Cleaning and Modeling Decisions

Several decisions were kept consistent throughout the project.

Keep the Original Data Intact

I created cleaned views rather than overwriting the raw CMS tables.

This preserved the original source data and made the cleaning process reproducible.

Use Facility ID as the Join Key

Facility ID was standardized as STRING across the three datasets before joining.

The overlap analysis confirmed that 177 facilities were represented across all three sources.

Preserve Missing Data

Unavailable CMS values were converted to NULL using SAFE_CAST().

I did not replace missing ratings or readmission scores with:

• zero;

• statewide averages;

• median values; or

• estimated values.

Doing so would have introduced information that was not present in the CMS source data.

Keep All 194 Hospitals in the Master Dataset

The general hospital table was used as the base of the final wide dataset.

LEFT JOINs preserved hospitals even when survey or readmission measures were unavailable.

An individual analysis therefore excludes a hospital only when the particular variable being analyzed is missing.

Reshape Long Data for Analysis

HCAHPS and unplanned-visit measures were initially stored vertically, with multiple rows per facility.

Conditional aggregation converted selected measures into separate hospital-level columns.

This made it easier to:

• compare hospitals;

• calculate correlations;

• build Tableau scatterplots;

• apply dynamic parameters; and

• analyze multiple outcomes using one hospital-level dataset.

Treat Statistical Relationships as Associations

The SQL correlation analysis and Tableau regressions are observational.

They show whether hospital-level measures vary together, but they do not show that one measure causes another.

This distinction became especially important when patient-experience metrics were compared with readmission rates. Some relationships reached statistical significance, but their R² values remained very small.

Final SQL Output

The main output of the cleaning and modeling process was:

hospital-readmissions-quality.hospital_quality.fact_il_hospital_performance

This hospital-level view combines:

Hospital Information

• Facility ID

• Facility Name

• City

• County

• ZIP Code

• Hospital Type

• Hospital Ownership

• Emergency Services

• CMS Overall Star Rating

Patient Experience

• HCAHPS Summary Star Rating

• HCAHPS Overall Star Rating

• Recommend Hospital Star Rating

• Nurse Communication Star Rating

• Doctor Communication Star Rating

• Medication Communication Star Rating

• Discharge Information Star Rating

• Cleanliness Star Rating

• Quietness Star Rating

• Nurse Communication % Always

• Doctor Communication % Always

• Medication Communication % Always

• Definitely Recommend %

30-Day Readmission Measures

• Heart Failure

• COPD

• Pneumonia

• AMI

• CABG

• Hip/Knee Replacement

This view served as the analytical bridge between the raw CMS datasets and the interactive Tableau story.