top of page
Search

Healthcare Dataset Analysis

  • Writer: Rajwol Khadka
    Rajwol Khadka
  • Mar 22
  • 3 min read

Updated: Apr 4

📌 Project Overview


This project focuses on analyzing a synthetic healthcare dataset using both SQL and Power BI to derive and visualize meaningful business insights. The primary aim was to examine year-over-year (YoY) trends across key hospital performance metrics, including:


  • Total revenue generated

  • Average billing per visit

  • Patient admission patterns

  • Average Length of Stay (LOS) in days


To ensure data integrity, Power Query was used to address missing or inconsistent records, normalize data, and engineer unique identifiers. Additional synthetic data was added to the 2024 records to ensure consistency across all measured years.


 

🧾 Dataset Description



 

🎯 Project Objectives


  • Analyze revenue, admissions, and LOS over time to reveal trends in hospital operations.

  • Compare YoY performance metrics to uncover areas of growth, inefficiency, or volatility.

  • Identify high-performing doctors by YoY billing increases.

  • Communicate findings through an interactive dashboard and written narrative.


 

📊 Analysis


Note: View the interactive dashboard as you go through the analysis to see how I visualized these results.


I began by examining total billing across each year to observe overall revenue trends and fluctuations.


SQL Query Logic:

  • CY_billing_amount: Total billing for the current year

  • PY_billing_amount: Previous year's total billing using the LAG() window function

  • Variance: Difference between current and previous year totals


SQL Query:


Results:











🔎 Observation:


  • Significant spike from 2019 to 2020, likely due to policy shifts or pandemic-related volume.

  • A notable $11 million decline in 2024, requiring deeper investigation.


 

Now using a similar query template and logic, we can we can obtain our other key metrics:

  • Average Billing per Visit

  • Unique Patients Admitted

  • Average Length of Stay (LOS)


SQL Query:


Results:





















🔎 Observation:


  • The average billing per visit remained relatively stable across all six years, consistently within the $25,400–$25,700 range.

  • Massive surge in patient volume from 2019 - 2020 (+3,870), likely linked to external events (e.g. COVID-19).

  • Spike in LOS in 2024, indicating possible systemic inefficiency / data integrity issues.


We now have our key metrics that will help us analyze hospital performance and understand strengths and pain points.


 

Now let's see which doctor generated the highest total billing amount for each year and generate a readable summary sentence using string concatenation.


SQL Query:


















Results:


🔎 Observations:


  • Michael Smith consistently led revenue performance in both 2022 and 2023, indicating a strong performance.

  • The post-2022 increase in top doctor billings supports the hypothesis that claim values and treatment costs are trending upward.


 

Now with my next query, I wanted to develop a multi-step SQL solution that could calculate YoY billing changes per doctor, rank them by the amount, and extract the top 5 doctors with the highest positive YoY growth for each year.


CTE 1:










I started off by calculating the total billing amount and grouping it by doctor and year.


CTE 2:












I then joined the YearlyBilling CTE to itself to compare each doctor's billing amount between two consecutive year. y1 is the current year, y2 is the previous year, and yoy_change represents the absolute increase/decrease in billing from the previous year.


CTE 3:

In this CTE, we filter out any doctors that did not record a previous year billing and rank them within each year by their YoY increase.


Full SQL Query:




















Results:


🔎 Observation:


  • Elizabeth Smith saw a $137K increase in 2020.

  • Mark Johnson led in 2024 with a $217K increase despite the hospital-wide revenue drop.

  • Ranking reveals performance outpacing organizational trends for certain doctors.

 

🔍Key Findings & Insights


  1. Revenue & Patient Count Growth (2019-2020)

    A $96M increase in revenue suggests a surge in claims, potentially driven by policy and pandemic impact


  2. Stable Pricing per Visit

    Despite volatility in total revenue, the average cost per visit hovered around $25,500, indicating strong pricing consistency.


  3. Doctor-Level Trends Override Hospital Trends

    Even as hospital revenue declined in 2024, Doctor Mark Johnson achieved a personal $217K billing increase — highlighting individual performance potential.


  4. LOS Spike in 2024

    The sharp increase in Length of Stay needs further analysis as it may indicate care complexity, a strain on resources, or inefficient discharge processes.


 

🖼️ Visualizing the Insights


If you're interested in seeing how I visualized the insights generated from these SQL queries, click here!



 
 
 

Σχόλια

bottom of page