hospital_patients_analysis

KO | EN

gitlite — search

#javascript #hacktoberfest #typescript #llm #golang #machine-learning #rust #deep-learning #linux #docker #nodejs #vue #pytorch #security

hospital_patients_analysis

(★ 14)

complete sql project

hospital_patients_analysis Latest Version Download

Download Latest Version (.zip)

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# Hospital PatientAnalysis

![Project Banner](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/banner.jpg)

An intermediate SQL analytics portfolio project focused on healthcare operations and patient records.

---

## 📑 Table of Contents

* [Project Overview](#project-overview)
* [Business Objectives](#business-objectives)
* [Dataset Description](#dataset-description)
* [Entity Relationship Diagram (ERD)](#entity-relationship-diagram-erd)
* [Phase 1: Database & Schema Design](#phase-1-database--schema-design)
* [Phase 2: Data Quality & Profiling](#phase-2-data-quality--profiling)
  * [Missing Data Audit](#missing-data-audit)
  * [Orphan Record Detection (Admissions vs. Procedures)](#orphan-record-detection-admissions-vs-procedures)
* [Phase 3: Core Patient Analysis – Data Cleaning & ETL](#phase-3-core-patient-analysis--data-cleaning--etl)
* [Phase 4: Intermediate Clinical & Operational Analysis](#phase-4-intermediate-clinical--operational-analysis)
  * [Departmental Visit Volume](#departmental-visit-volume)
  * [Age Group Segmentation](#age-group-segmentation)
* [Phase 5: Utilization & Mortality Analysis](#phase-5-utilization--mortality-analysis)
  * [Mortality Rates by Age Group](#mortality-rates-by-age-group)
  * [Super-Utilizer Identification](#super-utilizer-identification)
  * [Demographic Utilization Trends](#demographic-utilization-trends)
* [Phase 6: Financial & Insurance Analytics](#phase-6-financial--insurance-analytics)
  * [Payer Market Share by Race](#payer-market-share-by-race)
  * [Insurance Utilization by City](#insurance-utilization-by-city)
  * [Total Claims by Gender](#total-claims-by-gender)
  * [Multi-Insurance Coverage](#multi-insurance-coverage)
* [Phase 7: Clinical Efficiency & LOS Variance](#phase-7-clinical-efficiency--los-variance)
  * [Average LOS per Department](#average-los-per-department)
  * [LOS Outlier Detection](#los-outlier-detection)
* [Phase 8: High-Resource & Seasonality Analysis](#phase-8-high-resource--seasonality-analysis)
  * [Longest Hospital Stays](#longest-hospital-stays)
  * [Monthly Admission Trends](#monthly-admission-trends)
* [Key Insights](#key-insights)
* [Tools & Technologies](#tools--technologies)
* [How to Run the Project](#how-to-run-the-project)
* [Database Backups](#database-backups)

---

## Project Overview

This project builds an analytics layer on top of hospital patient records to support operational reporting and clinical insight generation.

It represents a **robust, real-world healthcare analytics exercise by SmartCruxBiz In the Study Group** built on a highly detailed hospital dataset.  
Due to the **breadth, depth, and complexity of the data**, the analytical process evolved beyond a rigid, textbook phase structure.

---
![Schema Overview](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/schema_overview.png)

---

## Business Objectives

- Analyze patient demographics and admission behaviour  
- Measure departmental utilization and efficiency  
- Identify high-risk and high-resource patients  
- Evaluate insurance usage and claim distributions  
- Translate SQL outputs into actionable healthcare insights

---

## Dataset Description

The dataset is sourced from Kaggle and represents a hospital patient record system including:

- Patient demographics  
- Admission and discharge records  
- Diagnoses and procedures  
- Encounter details  
- Insurance and billing information

All columns were reviewed prior to analysis to ensure correct interpretation.

---

## Entity Relationship Diagram (ERD)

![ER Diagram](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/erd_diagram.jpeg)

---

## Phase 1: Database & Schema Design

- Created a dedicated SQL Server database  
- Designed tables aligned with CSV structures  
- Assigned appropriate data types  
- Defined primary keys  
- Loaded all data successfully

![Database Schema](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/schema_overview.png)

---

## Phase 2: Data Quality & Profiling

### Missing Data Audit

All field were audited for missing values. The following results correspond to Patients_demographics, Procedure and Admission tables respectively.

![Missing Data Audit](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/data_quality_unpivot.png)

### Orphan Record Detection (Admissions vs. Procedures)

This check identifies **procedures that do not have a matching admission record**, ensuring referential integrity between clinical events.  **NILL**

---

## Phase 3: Core Patient Analysis – Data Cleaning & ETL

- Standardized demographic fields  
- Created derived attributes (age group, visit counts)  
- Prepared clean analytical datasets for reporting

---

## Phase 4: Intermediate Clinical & Operational Analysis

### Departmental Visit Volume

![Department Visits](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/department_visit_volume.png)

### Age Group Segmentation

My logic covered the three age group segmentation. But the analysis shows that the Pediatric (0–17 years) had no encounter.

![Age Group Segmentation](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/age_group_segmentation.png)

---

## Phase 5: Utilization & Mortality Analysis

### Mortality Rates by Age Group

![Mortality Rate](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/mortality_rate.png)

### Super-Utilizer Identification

Patients with more than five admissions were identified as high utilizers.

![Super Utilizers](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/super_utliizers.png)

### Demographic Utilization Trends

![Admissions by Race](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/admission_per_race.png)

---

## Phase 6: Financial & Insurance Analytics

### Payer Market Share by Race

![Insurance Contribution](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/insurance_contribution_rank.png)

### Insurance Utilization by City

![Insurance City Usage](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/insurance_usage_per_city.png)

### Total Claims by Gender

![Claims by Gender](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/total_claim_per_gender.png)

### Multi-Insurance Coverage

![Insurance per Patient](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/insurance_usage_per_patient.png)

---

## Phase 7: Clinical Efficiency & LOS Variance

### Average LOS per Department

![Average LOS](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/avg_los_by_department.png)

### LOS Outlier Detection

![LOS Outliers](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/los_outliers.png)

---

## Phase 8: High-Resource & Seasonality Analysis

### Longest Hospital Stays

![Longest Stays](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/longest_stays.png)

### Monthly Admission Trends

![Monthly Trends](https://raw.githubusercontent.com/ISAAC-Neumoton/hospital_patients_analysis/main/images/monthly_admission_trends.png)

---

## Key Insights

- Geriatric patients show higher mortality and readmission rates  
- Certain departments consistently exhibit longer lengths of stay  
- Admissions demonstrate clear seasonal patterns  
- Insurance utilization varies significantly across demographics

---

## Tools & Technologies

- Microsoft SQL Server (T-SQL)  
- Kaggle Healthcare Dataset  
- SQL Window Functions & CTEs  
- Git & GitHub

---

## How to Run the Project

1. Clone the repository  
2. Restore the database or create tables using the schema script  
3. Load CSV files if needed  
4. Execute the SQL analysis scripts  
5. Review outputs and insights

---

## Database Backups

This project includes **two SQL Server backup files (.bak)**:

- **Uncleaned Database Backup** — raw imported data  
- **Cleaned Database Backup** — validated and transformed data

These backups allow reviewers to restore and inspect both data states easily without rerunning the full ETL process.

---

hospital_patients_analysis

hospital_patients_analysis Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

hospital_patients_analysis

hospital_patients_analysis Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

Download README