SQL for Healthcare Data Analysis: Lecture Notes

Introduction

• SQL: Crucial skill for data analysts, especially in healthcare
• Goal: Teach SQL basics using simulated patient data
• Instructor: Josh Matlock, experienced data analyst in healthcare

Lecture Overview

1. Importance of SQL in data analytics
2. Installing PostgreSQL (installation for Windows and Mac)
3. Introduction to the healthcare data source: Cynthia
4. Loading data into a PostgreSQL database
5. Basic SQL commands and example problems

Importance of SQL

• SQL allows data analysts to pull, prepare, and report data efficiently
• Common tools used: Tableau and Power BI for visualizations
• SQL is like an advanced version of Excel but can handle much larger datasets
• Essential for handling large healthcare data

Installing PostgreSQL

Windows Installation

1. Visit PostgreSQL website
2. Navigate to 'Download' and select 'Windows'
3. Download and install the latest version
4. Set up the password and use default port 5432
5. Open PGAdmin 4 and log in using the password

Mac Installation

1. Visit PostgreSQL website
2. Navigate to 'Download' and select 'macOS'
3. Download PostgreSQL.app and follow installation instructions
4. Open terminal and run provided commands to set up
5. Download and install PGAdmin 4
6. Register the server in PGAdmin

Introduction to Cynthia

• Cynthia: Open-source tool for generating synthetic patient data
• Benefits: Models real-world scenarios; useful for educational purposes
• Data Included: Conditions, encounters, immunizations, and more
• Legal Note: Simplified version used to avoid legal issues with SNOMED codes

Loading Data into PostgreSQL

1. Open PGAdmin
2. Create new tables using SQL scripts from GitHub
3. Import data into tables (conditions, encounters, immunizations, patients)
4. Use import/export tools in PGAdmin and ensure data is loaded correctly

Basic SQL Commands

SELECT Statement

• SELECT * FROM public.patients (selects all columns from patients table)
• SELECT first, last, birth_date FROM public.patients (specific columns)*

WHERE Clause

• SELECT * FROM public.encounters WHERE encounter_class = 'inpatient'
• Can include multiple conditions using AND/OR*

GROUP BY Statement

• Used for aggregating data
• SELECT description, COUNT(*) FROM public.conditions GROUP BY description
• Adding ORDER BY and HAVING for further filtering and sorting*

Example Problems

1. Select all patients from Boston
2. Select all occurrences of chronic kidney disease with specific ICD-9 codes
3. List number of patients per city, excluding Boston, with at least 100 patients

Exploring Encounter Table

• Understanding types of encounters (ambulatory, emergency, inpatient, etc.)
• Using DISTINCT to find unique encounter classes

Introduction to Joins

• Purpose: Combine columns from different tables
• Types: LEFT JOIN, INNER JOIN, etc.
• Example: Joining immunizations table with patient details
• Important Concepts: Relationship cardinalities, ensures data integrity

Next Steps

• Study joins and relationship cardinalities further
• Functions in SQL
• Common table expressions (CTEs)
• Recommended videos and resources for extended learning

Conclusion

• SQL is a vital skill for data analysts in healthcare
• Future tutorials will cover more advanced topics
• Encouraged to check out further resources and video tutorials on data analysis in healthcare