Moshraf Hossain

Moshraf Hossain

Let’s talk about data! Reach out via the links below.

Introduction

The project is to demonstrate the use of SQL to extract meaningful insights from a hospital database. This includes analysing patient demographics, hospital stays, risk factors, and other relevant metrics to improve hospital operations and patient care.

Datasets

  • appointment_analysis: Contains information about patient appointments including date, time, and department.
  • hospital_records: Includes details on patient hospital stays, such as duration and department.
  • outpatient_visits: Records outpatient visits with diagnosis, medication prescribed, and smoker status.
  • patients_table: Stores patient demographic information.
  • lab_results: Contains lab test results.

Key SQL Queries and Their Insights

1. Patients in the Pediatrics Department

SELECT
    patient_id,
    department_name
FROM appointment_analysis
WHERE department_name = 'pediatrics';

Insight: Identifies which patients have appointments in the Pediatrics department, aiding in resource management and patient distribution analysis.

2. Average Days in Cardiology Department

SELECT AVG(days_in_the_hospital) AS average_days_cardiology
FROM hospital_records
WHERE department_name = 'Cardiology';

Insight: Calculates the average length of stay for patients in the Cardiology department, helping to assess efficiency and recovery times.

3. Average Days per Department

SELECT
    department_name,
    AVG(days_in_the_hospital) AS avg_days_per_department
FROM hospital_records
GROUP BY department_name
ORDER BY avg_days_per_department DESC;

Insight: Lists the average patient stay across all departments, highlighting departments with longer or shorter stays.

4. Patient Stay Categorization

SELECT
    patient_id,
    days_in_the_hospital,
    CASE
        WHEN days_in_the_hospital <=3 THEN 'Short'
        WHEN days_in_the_hospital <= 5 THEN 'Medium'
        ELSE 'Long'
    END AS stay_category
FROM hospital_records;

Insight: Categorizes patients based on the length of their hospital stay, aiding in resource utilization analysis.

5. Count of Patients by Stay Category

SELECT
    CASE
        WHEN days_in_the_hospital <=3 THEN 'Short'
        WHEN days_in_the_hospital <= 5 THEN 'Medium'
        ELSE 'Long'
    END AS stay_category,
    COUNT(*) AS number_of_records
FROM hospital_records
GROUP BY
    CASE
        WHEN days_in_the_hospital <=3 THEN 'Short'
        WHEN days_in_the_hospital <= 5 THEN 'Medium'
        ELSE 'Long'
    END;

Insight: Provides a count of patients in each stay category, useful for capacity planning.

6. Day of the Week from Appointment Date

SELECT
  appointment_date,
  DAYOFWEEK(appointment_date) AS day_of_the_week
FROM appointment_analysis;

Insight: Extracts the day of the week from appointment dates, useful for identifying patterns in appointment scheduling and patient flow on different days.

7. Day of Hour from Appointment Time

SELECT
appointment_time,
HOUR(appointment_time) AS appointment_hour
FROM appointment_analysis;

Insight: Provides the hour of appointments, which can help in analyzing peak times and scheduling efficiency.

8. Safety Concerns Based on Smoking and Medication

SELECT
    patient_id,
    diagnosis,
    medication_prescribed,
    smoker_status,
    CASE
        WHEN smoker_status = 'Y' AND medication_prescribed IN ('Insulin', 'Metformin', 'Lisinopril') THEN 'Potential Safety Concern: Smoking and Medication Interactions'
        ELSE 'No Safety Concern Identified'
    END AS 'safety_concern'
FROM outpatient_visits;

Insight: Flags potential safety concerns related to smoking and specific medications, critical for patient safety management.

9. Risk Classification Based on BMI and Family History

SELECT
    patient_id,
    patient_name,
    bmi,
    family_history_of_hypertension,
    CASE
        WHEN bmi >= 30 AND family_history_of_hypertension = 'Yes' THEN 'High Risk'
        WHEN bmi >= 25 AND family_history_of_hypertension = 'Yes' THEN 'Medium Risk'
        ELSE 'Low Risk'
    END risk_category
FROM hospital_records;

Insight: Classifies patients into risk categories based on BMI and family history, supporting targeted health interventions.

10. Demographic Characteristics of Diabetic Patients

SELECT
    gender,
    CASE
        WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 18 AND 30 THEN '18-30'
        WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 31 AND 50 THEN '31-50'
        WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 51 AND 70 THEN '51-70'
        ELSE '71+'
    END AS age_group,
    COUNT(*) AS patient_count
FROM patients_table AS p
INNER JOIN outpatient_visits AS ov
ON p.patient_id = ov.patient_id
WHERE diagnosis = 'Diabetes'
GROUP BY
    gender,
    CASE
        WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 18 AND 30 THEN '18-30'
        WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 31 AND 50 THEN '31-50'
        WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 51 AND 70 THEN '51-70'
        ELSE '71+'
    END;

Insight: Provides demographic characteristics of diabetic patients, aiding in understanding population distribution and tailoring healthcare services.

Conclusion

This documentation highlights essential SQL queries used in analyzing hospital data. These queries help in patient care management, risk assessment, and demographic analysis, contributing to better decision-making in hospital operations.

2025

Sales Insights

Introduction This SQL project involves analyzing the sales and customer data of H+ Sport, a fictitious company that sells nutritional products and active lif...

Back to Top ↑

2023

The B.A.R.N. Framework

Introduction Writing about your projects is crucial to giving context for the work. Writing an impactful article requires a structured approach that captures...

Healthcare Insights

Introduction The project is to demonstrate the use of SQL to extract meaningful insights from a hospital database. This includes analysing patient demographi...

Design Basics

Your brain has two systems, a slow analytical side and a quick, instinctive “gut” side. For the most part, the instinctual side is lazy, conserving its energ...

Part 1: Color Theory

“Color does not add a pleasant quality to design—it reinforces it.” — Pierre Bonnard

Part 3: Color Theory

Tips for Designing a Color Palette In the first two sections of this book, Color Limitations and Color and Emotions, we looked at some interesting facts abou...

Part 2: Color Theory

Color and Emotions Mention Thanksgiving to any American and they will paint a picture in their head with gold, orange, red, and brown hues. It will also like...

Collaborate the Game

Background In 2019 I was awarded a Department of Education SBIR grant to build an educational board game about collaboration. Though the game would eventuall...

Shuffling Cards

To see a preview of this project go to Shuffling Cards on Codespaces and select the index.html file and then go live on the status bar.

Alika’s Treehouse

Alika’s Treehouse Background This project won the Capital One Women In Tech Demo Day. The challenge, “Help young women of color see themselves as a Computer ...

Part 1: How to win at a hackathon

Time and pressure can change almost anything from what it was into what it has become. Time will change, “Caterpillar into butterflies, sand into pearls...

Part 2: How to win at a hackathon

“You can’t connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your ...

Accelerating your Career

Looking back on my career, there are a few things I wish I had done earlier to boost my career progression and enhance my marketability. In this article, I w...

Part 3: How to win at a hackathon

“No struggle, no success! The strongest thunder strikes often bring the heaviest rainfall! The weight of your fulfillment depends on how wide you cast you...

Back to Top ↑