Learning Objectives
- Identify the six core healthcare tables and explain what each one stores and why it matters for analysis.
- Distinguish between patient-level and encounter-level data and explain how the hierarchy shapes SQL query design.
- Explain the purpose and format of ICD-10, CPT, and HCPCS and describe how each coding system is used in healthcare queries.
- Identify which table to query for clinical, financial, and operational questions.
SQL for Healthcare Professionals is structured as a progressive journey — from foundational data concepts through clinical querying to advanced population health and operational reporting. Each part builds directly on the one before it.
How healthcare data is organized across core tables, the distinction between patient-level and encounter-level data, and the three major clinical coding systems — ICD-10, CPT, and HCPCS.
Filtering by ICD-10 codes, joining encounters to diagnoses and procedures, and extracting clinical cohorts such as diabetes registries, ED patient groups, and preventive care gaps.
Length-of-stay calculations, readmission logic, population health aggregates, risk stratification, scheduling analytics, and claims cost analysis — producing management-level insights.
By the end of all three parts, learners will have a complete, practical SQL foundation tailored to the real reporting and analytical needs of healthcare professionals.
Healthcare data is structured around patients, their encounters, and the clinical and financial events tied to those encounters. Every query in this course draws from one or more of these six tables.
Population health · Cohort definitions · Risk stratification · Demographic analysis. Almost every healthcare query starts here — by identifying which patients are being analyzed.
Length of stay · Readmission tracking · Utilization patterns · Encounter type analysis (inpatient, outpatient, ED). The backbone table for almost all utilization reporting.
Cohort building · Risk scoring · Population health analytics · Chronic disease registries. Every clinical filter — such as "all diabetes patients" — runs through this table.
Quality metrics · Utilization reporting · Cost modeling · Preventive care gap analysis. Answers questions like "which patients received an A1C test this year?"
Cost analytics · Payer and provider performance · Revenue cycle insights · Cost-per-patient and cost-per-encounter reporting.
No-show prediction · Operational efficiency · Access-to-care metrics · Provider productivity reporting. Status values include Completed, No Show, and Cancelled.
How the Tables Connect
All six tables connect through shared keys — patient_id links patients to encounters, and encounter_id links encounters to diagnoses, procedures, and claims. Understanding this chain is what makes multi-table queries possible.
Before writing any query, always ask: Am I analyzing people, or am I analyzing visits? The answer determines which table to start from and how every join should be structured.
Stores information that does not change per visit — demographics, insurance type, primary care provider, social determinants. Use for population health, cohort definitions, and risk stratification.
Stores information that changes every time a patient interacts with the system — admit/discharge dates, encounter type, diagnoses, procedures. Use for length of stay, readmissions, and utilization.
The Full Data Hierarchy
A patient can have many encounters. Each encounter can generate many diagnoses, procedures, and claims. Joining across these levels without understanding the multiplying effect is the most common source of incorrect results in healthcare SQL.
| Level | Table | Relationship | Example Question |
|---|---|---|---|
| Patient | patients | 1 row per person | "Who are our active patients?" |
| Encounter | encounters | Many rows per patient | "How many visits did each patient have?" |
| Diagnosis | diagnoses | Many rows per encounter | "Which patients had a diabetes diagnosis?" |
| Procedure | procedures | Many rows per encounter | "Who received an EKG this year?" |
| Claim | claims | Many rows per encounter | "What is the total cost per patient?" |
Always verify the row count after every join. If the result has more rows than expected, the join is likely crossing a level boundary incorrectly — producing duplicated patient or encounter records.
Healthcare data relies on three standardized coding systems. Memorizing individual codes is not required — what matters is understanding what each system represents and how it is used to filter and group data in SQL. Every clinical cohort, quality metric, and cost report is built on at least one of these three systems.
Represents: Diagnoses — diseases, symptoms, conditions
Format: Letter + numbers + optional decimal
Why analysts use it: To filter patients into clinical cohorts
Represents: Clinical procedures — what was done
Format: 5 digits, all numbers
Why analysts use it: To measure utilization and quality
Represents: Supplies, injections, ambulance, equipment
Format: 1 letter + 4 digits
Why analysts use it: To analyze non-physician service costs
ICD-10 Code Patterns in SQL
ICD-10 codes are hierarchical — a prefix captures an entire condition category. Knowing when to use an exact match versus a prefix wildcard is one of the first practical SQL skills in healthcare analytics.
| Condition | Code / Pattern | SQL Filter | Captures |
|---|---|---|---|
| Hypertension | I10 | = 'I10' | Exact match only |
| Type 2 Diabetes (all) | E11% | LIKE 'E11%' | E11.9, E11.65, E11.40 … |
| Pneumonia, unspecified | J18.9 | = 'J18.9' | Exact match only |
| COPD (all) | J44% | LIKE 'J44%' | J44.0, J44.1, J44.9 … |
| Heart Failure (all) | I50% | LIKE 'I50%' | I50.1, I50.2, I50.9 … |
Using = 'E11' instead of LIKE 'E11%' when filtering for diabetes will miss nearly all cases. Most real-world ICD-10 codes include a decimal sub-type — always use LIKE with a prefix when filtering for a condition category.
Healthcare data does not originate in a single system. Clinical, operational, and financial information flows across multiple interconnected platforms throughout a single patient interaction. Understanding that flow clarifies which table to query for each type of question.
- Patient is registered — demographic and insurance data is captured in the patients table. A unique patient_id is assigned.
- Appointment is scheduled — the visit is recorded in appointments with a status of Scheduled.
- Patient is seen — an encounter record is created in encounters. The appointment status updates to Completed.
- Diagnoses are documented — ICD-10 codes are recorded in diagnoses, linked by encounter_id.
- Procedures are performed — CPT and HCPCS codes are recorded in procedures, also linked by encounter_id.
- Claim is submitted — a financial record is created in claims, capturing billed, allowed, and paid amounts.
Which Table Answers Which Question
| Domain | Example Question | Primary Table(s) |
|---|---|---|
| Clinical | "How many patients have a diabetes diagnosis?" | diagnoses + encounters |
| Clinical | "Which procedures were performed during inpatient stays?" | procedures + encounters |
| Financial | "What is the average paid amount per encounter type?" | claims + encounters |
| Financial | "Which patients generated the highest total cost?" | claims |
| Operational | "What is the average length of stay for inpatient visits?" | encounters |
| Operational | "Which appointment types have the highest no-show rate?" | appointments |
Always identify the domain — clinical, financial, or operational — before selecting a table. That domain determines the primary table. Every other table in the query is either a join to add context or a filter to narrow results.
Overview & Introduction — Key Takeaways
Five foundational principles from this introductory section.
Patients, encounters, diagnoses, procedures, claims, and appointments — knowing each table's level and purpose is the starting point for every healthcare query.
Patient-level has one row per person. Encounter-level has many rows per person. Joining correctly across that boundary prevents the most common errors in healthcare analytics.
ICD-10 identifies diagnoses, CPT documents procedures, and HCPCS covers supplies and non-physician services. These systems are the primary filters in every clinical query.
Clinical, financial, and operational questions each map to a distinct set of tables. Identifying the correct domain before writing a query is one of the most practical skills in the course.
Conceptual clarity prevents analytical errors. A solid grasp of data structure, table levels, and coding systems produces accurate, trustworthy results from the very first query.