Case Study · SQL & Visualization

Medicare Hospital SQL Analysis

A 28-query descriptive PostgreSQL analysis of 6,000+ Medicare-registered U.S. hospitals, covering hospital distribution, quality ratings, ownership, and reported emergency services in a five-dashboard Tableau story.

SQL PostgreSQL Tableau Python Pandas Window Functions CTEs Healthcare Analytics CMS Dataset

↗ Live Dashboard ⌥ SQL Notebook ⌥ GitHub Repo

6K+Hospitals Analyzed

28SQL Queries

5Tableau Dashboards

50+States & Territories

Live Dashboard

CMS Hospital Dashboard — Tableau Public

↗ Open full screen

Loading dashboard...

⚡ Fully interactive — navigate all 5 story pages using the tabs above the dashboard · open in Tableau Public for best experience

Story Overview

↗ Open in Tableau

Story Overview — 5-dashboard narrative with key findings and navigation guide

Hospital Counts and Quality Ratings by State

Hospital Counts & Quality Ratings — Choropleth map with toggle between count and average rating per state

Emergency Services and Deviation by State

Emergency Coverage & Deviation — State rankings by emergency service % and diverging bar vs. national average

Type & Ownership Matrix — Treemap by state + cross-tab of hospital type vs. ownership structure

SQL Query Showcase

Query 1 of 28

⌥ Full notebook

1 / 28

The Problem

The CMS Hospital General Information dataset contains thousands of facility records in a flat-file format. The project uses SQL to turn that snapshot into reviewable summaries and dashboard-ready tables.

The analysis asks descriptive questions: How do hospital counts, reported emergency services, ownership types, and available quality ratings vary by state and hospital type?

Approach

Data Ingestion & Cleaning

Loaded the CMS Hospital General Information dataset into PostgreSQL. Used Python and Pandas to scaffold and clean the data — handling missing ratings, standardizing state codes, and preparing the table for SQL analysis.

PostgreSQLPandasData CleaningPython

28-Query SQL Analysis

Built a structured notebook of 28 interview-ready SQL queries covering the full spectrum — from basic aggregations to window functions, CTEs, pivot-style queries, set operations, and string/date functions. Each query is framed around a real stakeholder question.

Window FunctionsCTEsPivot QueriesLAG / LEADSet Operations

Geographic & Ownership Analysis

Aggregated hospitals by state, type, and ownership. Compared each state's count of hospitals reporting emergency services with the mean count across states and territories, without treating those counts as capacity or access measures.

GROUP BYDeviation AnalysisGeographic Aggregation

5-Dashboard Tableau Story

Translated SQL outputs into a 5-page interactive Tableau story — a choropleth quality map, emergency coverage rankings, diverging deviation bars, type/state treemap, and an ownership cross-tab matrix. Designed for non-technical stakeholders.

TableauStory PointsChoropleth MapDashboard Design

Key Findings

Texas, California, and Florida have the largest counts of hospitals reporting emergency services: 383, 289, and 183, respectively.

97.2% of Critical Access Hospitals and 91.4% of Acute Care Hospitals report emergency services in this dataset.

Reported emergency-service percentages are lower among Children's Hospitals (58.5%) and Psychiatric Hospitals (13.4%).

Voluntary non-profit private ownership is the largest category among Acute Care, Children's, and Critical Access Hospitals; proprietary ownership is the largest category among Psychiatric Hospitals.

Analytical Value

This project demonstrates a complete descriptive analyst workflow: raw federal data → structured SQL questions → documented outputs → a visual story for non-technical review.

The results can help frame follow-up questions about facility distribution and reported services, but they do not by themselves identify service capacity, healthcare access, or investment priorities.

Limitations

This is a descriptive analysis of a CMS dataset snapshot and does not measure causal relationships. Hospital counts do not represent bed capacity, service volume, travel distance, or population-level healthcare access.

State comparisons are not population-adjusted, percentage differences were not tested for statistical significance, and missing quality ratings can affect comparisons across states, hospital types, and ownership categories.